DROP VIEW IF EXISTS OTBranchTracking
GO 
Create View OTBranchTracking
as
WITH AggregatedTime AS (
    SELECT 
        ta.CompanyID, 
        ta.OwnerID,
        ta.WeekID,
        b.BAccountID AS EmployeeBAccount, 
        b.AcctCD AS EmployeeCD, 
        branch.AcctCD AS BranchCD, 
        branch.AcctName AS BranchName,
        SUM(ta.TimeSpent) AS TotalTimeSpent
    FROM PMTimeActivity ta
    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 ta.EarningTypeID not in ('HOL', 'PTO')
    and ta.IsCorrected = 0
    GROUP BY 
        ta.CompanyID,
        ta.OwnerID,
        ta.WeekID,
        b.BAccountID,
        b.AcctCD,
        branch.AcctCD,
        branch.AcctName
),
EmployeeTime AS (
    SELECT 
        at.CompanyID,
        at.OwnerID,
        at.EmployeeBAccount,
        at.EmployeeCD,
        at.BranchCD,
        at.BranchName,
        at.WeekID,
        at.TotalTimeSpent,
        CASE 
            WHEN at.TotalTimeSpent > 2400 THEN at.TotalTimeSpent - 2400 
            ELSE 0 
        END AS OverTimeInMinutes,
        CASE 
            WHEN at.TotalTimeSpent > 2400 THEN 2400 
            ELSE at.TotalTimeSpent 
        END AS RegularTimeInMinutes
    FROM AggregatedTime at
),
BranchTime AS (
    SELECT 
        et.CompanyID, 
        et.BranchCD, 
        et.WeekID,
        SUM(et.TotalTimeSpent) AS TotalTimeSpent,
        SUM(et.OverTimeInMinutes) AS OverTimeInMinutes,
        SUM(et.RegularTimeInMinutes) AS RegularTimeInMinutes,
        CASE 
            WHEN SUM(et.TotalTimeSpent) = 0 THEN NULL
            ELSE ROUND(
                CAST(SUM(et.OverTimeInMinutes) AS DECIMAL(10,2)) * 100.0 / 
                CAST(SUM(et.TotalTimeSpent) AS DECIMAL(10,2)), 2
            ) END AS OvertimePercent,
			CASE 
            WHEN SUM(et.TotalTimeSpent) = 0 THEN NULL
            ELSE ROUND(
                CAST(SUM(et.OverTimeInMinutes) AS DECIMAL(10,4)) / 
                CAST(SUM(et.TotalTimeSpent) AS DECIMAL(10,4)), 4
            )
        END AS OvertimeRatio   
    FROM EmployeeTime et
    GROUP BY et.CompanyID, et.BranchCD, et.WeekID
)
SELECT 
    CompanyID,
    BranchCD,
    WeekID,
    TotalTimeSpent,
    RegularTimeInMinutes,
    OverTimeInMinutes,
    OvertimePercent,
	OvertimeRatio
FROM BranchTime;