AcumaticaSQLQueriesAndFunctions/NonWProjectBranchTracking.sql

DROP VIEW IF EXISTS NonWProjectBranchTracking
GO 
Create View NonWProjectBranchTracking
as
    SELECT 
        ta.CompanyID, 
        ta.WeekID,
        branch.AcctCD AS BranchCD, 
        branch.AcctName AS BranchName,
        SUM(ta.TimeSpent) AS TotalTimeSpent, 
        SUM(CASE 
              WHEN SUBSTRING(c.ContractCD, 1, 1) = 'W' 
              THEN ta.TimeSpent 
              ELSE 0 
           END) as WProjectTimeSpent,
        SUM(CASE 
              WHEN SUBSTRING(c.ContractCD, 1, 1) != 'W' 
              THEN ta.TimeSpent 
              ELSE 0 
           END) as NonWProjectTimeSpent,
               CASE 
        WHEN SUM(ta.TimeSpent) = 0 THEN NULL
        ELSE CAST(SUM(CASE 
                          WHEN SUBSTRING(c.ContractCD, 1, 1) != 'W' 
                          THEN ta.TimeSpent 
                          ELSE 0 
                      END) AS DECIMAL(18, 4)) 
             / CAST(SUM(ta.TimeSpent) AS DECIMAL(18, 4))
    END AS NonWProjectPercent
    FROM PMTimeActivity ta
    join Contract c 
    	on ta.CompanyID = c.CompanyID and ta.ProjectID = c.ContractID
    JOIN EPTimeCard tc 
        ON ta.CompanyID = tc.CompanyID AND ta.TimeCardCD = tc.TimeCardCD and tc.WeekID = ta.WeekID
    JOIN EPEmployee e 
        ON tc.CompanyID = e.CompanyID AND tc.EmployeeID = e.BAccountID
    Join InventoryItem i
        on e.CompanyID = i.CompanyID and e.LabourItemID = i.inventoryID
    JOIN BAccount b 
        ON e.CompanyID = b.CompanyID AND e.BAccountID = b.BAccountID
    JOIN BAccount branch 
        ON b.CompanyID = branch.CompanyID AND branch.BAccountID = b.ParentBAccountID
    where i.InventoryCD in ('L109', 'L020', 'L101', 'L113', 'L117', 'L121', 'L123', 'L124', 'TECH', 'L021')
    and ta.EarningTypeID not in ('HOL', 'PTO')
    and ta.IsCorrected = 0
    GROUP BY 
        ta.CompanyID,
         ta.WeekID,
        branch.AcctCD,
        branch.AcctName