I've been trying to build a running (cumulative) calculation up to a certain value, on two columns. The problem I'm facing:

1. Calculate overtime hours for employees ('EmpID') for a specific pay period ('Grouping'), spread over 5 weeks ('YearWeek'). The employees can claim a maximum of 20 hours of cumulative overtime.

2. Each column carries different premiums ('OT30': 30%, 'OT50': 50%), so I cannot combine them.

3. I need to obtain a total of maximum 20 hours in new columns 'OT30_NT' and 'OT50_NT'.

   Input data below:

I've been trying to build a running (cumulative) calculation up to a certain value, on two columns. The problem I'm facing:

1. Calculate overtime hours for employees ('EmpID') for a specific pay period ('Grouping'), spread over 5 weeks ('YearWeek'). The employees can claim a maximum of 20 hours of cumulative overtime.

2. Each column carries different premiums ('OT30': 30%, 'OT50': 50%), so I cannot combine them.

3. I need to obtain a total of maximum 20 hours in new columns 'OT30_NT' and 'OT50_NT'.

   Input data below:

Desired Output: - Max 20 hours condition achieved

My calculation attempt below:

1. unpivot table to convert 'OT30' and 'OT50' columns to rows (ending up with the columns 'Metric' and 'Value')
2. add a RowNumber column to get a proper cumulative sum (ending up with the 'RowNo' column, to be used in the Partition By / Order By clause)
3. get a cumulative sum by using the 'Partition By' clause (ending up with the 'OTCum' column, to determine where the max value of 20 hours is achieved)
4. I have tried various CASE clauses in the 'CalcAttempt' column, but I cannot figure out how to achieve the maximum limit of 20 hours.

SQL code here:

SELECT 
    m.[EmployeeId]
    ,m.[Grouping]
    ,m.[GroupingForOrder]
    ,m.[Metric]
    ,m.RowNo
    ,m.Value
    ,SUM([Value]) OVER (PARTITION BY [EmployeeID], [Grouping] ORDER BY RowNo) AS 'OTCum'
    ,CASE
        WHEN SUM([Value]) OVER (PARTITION BY [EmployeeID], [Grouping] ORDER BY RowNo)<=20 THEN Value 
    ELSE 20-SUM([Value]) OVER (PARTITION BY [EmployeeID], [Grouping] ORDER BY RowNo) END AS CalcAttempt

FROM

-- Second Query to add Row Numbers to get the correct Cumulative Value
   (
    SELECT
        [EmployeeId]
        ,[Grouping]
        ,[GroupingForOrder]
    ,[Metric]
    ,[Value]
    ,ROW_NUMBER() OVER (PARTITION BY EMployeeID, Grouping ORDER BY GroupingForOrder) AS 'RowNo'
    FROM
        (
-- Initial query to pivot OT30 and OT50 Values
       SELECT [EmployeeId]
       ,[Grouping]
       ,[GroupingForOrder]
       ,[OT30]
       ,[OT50]
       FROM [lookups].[Timekeeping_WeeklyBreakdown]
       ) AS SourceTable
       UNPIVOT
       (Value for Metric IN ( 
       [OT30]
      ,[OT50]
       )) AS ALIAS
    )AS m

   

If I can get the 'CalcAttempt' column to work as per the above comments, I can then pivot by 'Metric' and finalize calculations. Of course, assuming there's another solution that doesn't require me to do the above.

Using SQL Azure 12.0.2000.8.

Any suggestions would be highly appreciated.

• sql
• sql-server
• azure-sql-database
• cumulative-sum
• partition-by

• Thank you, hkt, serg and valnik. Interesting solutions. I've tested all of them, and they worked perfectly. I will upload an entire dataset and do some performance testing to decide which one to use. Thank you very much for your help. – Dan VDM Commented Feb 16 at 19:01

3 Answers 3

You are indeed on the right track.

1. Unpivot OT

2. calculate cumulative sum of OT

3. case expression to determine the claimable OT value.

   The logic :-

   c.CumulativeOT <= @max_hours : max_hours is greater than Cumulative OT full OT amount is claimable

   c.CumulativeOT - c.OT <= @max_hours : when reach this condition, Cumulative is > @max_hours, so it need to check for previous row Cumulative value is <= @max_hours. (c.CumulativeOT - c.OT is previous row Cumulative value.) and calculate the balance of @max_hours available = @max_hours - c.CumulativeOT + c.OT

   declare @max_hours int = 20; -- Max 20 hours
   
   with cte as
   (
      select EmpID, [Grouping], [YearWeek], Seq, OT30, OT50, OT,
             -- Calculate cumulative sum of OT
             CumulativeOT = sum(OT) over (partition by EmpID, [Grouping]
                                              order by [YearWeek], Seq)
      from   YourTable t            
             cross apply -- Unpivot OT30 & OT50
             (
                 values (1, OT30), (2, OT50)
             ) o (seq, OT)
    ),
   cte2 as
   (
      select *,
             ClaimableOT = case when c.CumulativeOT <= @max_hours
                                then c.OT
                                when c.CumulativeOT - c.OT <= @max_hours
                                then @max_hours - c.CumulativeOT + c.OT
                                else 0
                                end
      from   cte c
   )
   -- Pivot back the OT column
   select EmpID, [Grouping], YearWeek, 
          OT30, OT50,       -- original OT value
          ClaimableOT30 = SUM(case when seq = 1 then ClaimableOT else 0 end),
          ClaimableOT50 = SUM(case when seq = 2 then ClaimableOT else 0 end)  
   from   cte2
   group by EmpID, [Grouping], YearWeek, OT30, OT50
   order by EmpID, [Grouping], YearWeek;
   

Provided only two OT-kind columns are in the table you can skip unpivot/pivot steps at a price of a bit more complex CASEs.

with cum as (
  select [EmpId]
    ,[Grouping]
    ,[YearWeek]
    ,[OT30]
    ,[OT50]
    ,coalesce(SUM([OT30]+[OT50]) OVER (PARTITION BY [EmpID], [Grouping] ORDER BY [YearWeek] rows between unbounded preceding and 1 preceding),0) AS [OTCum-1]
  from tbl
)
select  [EmpId]
  ,[Grouping]
  --,[YearWeek]
  ,case when [OTCum-1] >= @max_hours then 0 
        when [OTCum-1] + [OT30] >= @max_hours then @max_hours - [OTCum-1] 
        else [OT30] end [OT30_NT]
  ,case when [OTCum-1] + [OT30] >= @max_hours then 0 
        when [OTCum-1] + [OT30] + [OT50] >= @max_hours then @max_hours - ([OTCum-1] + [OT30])
        else [OT50] end [OT50_NT]
from cum
order by  [EmpId], [Grouping], [YearWeek]

Sql Sever fiddle

1. Calculate running total (OT30+OT50) -> tSum.
2. On row where tSum>20 if (tSum-OT30-OT50)<20 calculate new OT30_NT and OT50_NT.
   Other rows where tSum>20 - set OT30_NT and OT50_NT to 0.
   First change OT50. If this is not enough, change OT30.

For OT30

 ,case when tSum<@maxOverTimeHours then OT30  -- nothing to do
       when tSum>=@maxOverTimeHours and (tSum-OT30-OT50)<@maxOverTimeHours then  
      -- reduce OT50 and save OT30
         case when (tSum-OT50)<@maxOverTimeHours then OT30
      -- reduce OT50 and OT30   
         else OT30-((tSum-OT50)-@maxOverTimeHours)
        end
     else 0
     end OT30_NT

For OT50

    ,case when tSum<@maxOverTimeHours then OT50
       when tSum>=@maxOverTimeHours and (tSum-OT30-OT50)<@maxOverTimeHours then
      --reduce OT50 and save OT30
         case when (tSum-OT50)<@maxOverTimeHours then OT50-(tSum-@maxOverTimeHours)
      -- reduce OT50 to 0 and reduce OT30   
         else 0
         end
     else 0
     end OT50_NT

See example
Test data for several cases (Grouping):

declare @maxOverTimeHours int = 20;
select *
  ,sum(OT30_NT+OT50_NT)over(partition by EmpID,Grouping order by YearWeek) SumOT_NT
from( -- correct OT50 and/or OT30
  select *
    ,case when tSum<@maxOverTimeHours then OT30
       when tSum>=@maxOverTimeHours and (tSum-OT30-OT50)<@maxOverTimeHours then
         case when (tSum-OT50)<@maxOverTimeHours then OT30
         else OT30-((tSum-OT50)-@maxOverTimeHours)
        end
     else 0
     end OT30_NT
    ,case when tSum<@maxOverTimeHours then OT50
       when tSum>=@maxOverTimeHours and (tSum-OT30-OT50)<@maxOverTimeHours then
         case when (tSum-OT50)<@maxOverTimeHours then OT50-(tSum-@maxOverTimeHours)
         else 0
         end
     else 0
     end OT50_NT
  from( -- running total
    select *
      ,sum(OT30+OT50)over(partition by EmpID,Grouping order by YearWeek) tSum
    from Timekeeping_WeeklyBreakdown
   )a
)b

fiddle

More short query

declare @maxOverTimeHours int = 20;

select *
  ,case when rsum>(OT30+OT50) then 0
     when rSum>0 then 
       case when rSum<=OT50 then OT30
            when rSum>OT50 then OT30-(rSum-OT50)
       else 0
       end
   else OT30
   end OT30_NT
  ,case when rsum>(OT30+OT50) then 0 
      when rSum>0 then 
       case when rSum>=OT50 then 0
       else OT50-rSum
       end
   else OT50
   end OT50_NT
from(
 select *
  ,sum(OT30+OT50)over(partition by EmpID,Grouping order by YearWeek)-@maxOverTimeHours rSum
 from Timekeeping_WeeklyBreakdown
)a

fiddle