SQL 2016 - Need help with complex calculations based on paycodes !

  • Hello Friends,

    I 'm in need of some SQL help please, I have SQL examples below with the following conditions:

    All REG plus SICK hours cannot exceed 40 hours in that week.

    The calculation for REG hours is: total no. of hours in a Cost Center Divided By Total REG Hours regardless of Cost Center  times (40 minus SICK hours)

    In first example below, the REG hours are 50 with 8 SICK hours.

    REG hours in Cost Center 123 = 50 hours

    SICK hours regardless of Cost Center = 8 hours

    So it would be REG hours  = 50/50 x (40 -8) = 32 Hours

    Then SICK hours = 8, so the total hours sent that week = 40

    Second SQL example below, the REG hours are 50 with 8 SICK hours.

    REG hours in Cost Center 123 = 30 hours

    REG hours in Cost Center 456 = 20 hours

    SICK hours regardless of Cost Center = 8 hours

    So it would be REG hours  in Cost Center 123 = 30/50 x (40 -8) = 19.2 Hours

    And REG hours  in Cost Center 456 = 20/50 x (40 -8) = 12.8 Hours

    And SICK hours  = 8 hours, all these three transactions will make it 40 hours

    3rd and last example below, the REG hours are 62.

    REG hours in Cost Center 123 = 30 hours

    REG hours in Cost Center 456 = 20 hours

    REG hours in Cost Center 789 = 12 hours

    So it would be REG hours  in Cost Center 123 = 30/62 x (40 -0) = 19.2 Hours

    And REG hours  in Cost Center 456 = 20/62 x (40 -0) = 12.8 Hours

    And REG hours  in Cost Center 789 = 12/62 x (40 -0) = 8 Hours, all these three transactions will make it 40 hours

    Thank you all in advance and god bless !

    -- Example One
    WITH SampleData (PERSON, [COSTCENTER],[AMOUNT], [PAYCODE],[DATE]) AS

    (
    SELECT 101,'123','12.00','REG','04/26/2021'

    UNION ALL SELECT 101,'123','12.00','REG','04/27/2021'

    UNION ALL SELECT 101,'123','13.00','REG','04/28/2021'

    UNION ALL SELECT 101,'123','13.00','REG','04/29/2021'

    UNION ALL SELECT 101,'123','08.00','SICK','04/30/2021'


    )

    SELECT * FROM SampleData;

    -- Example Two
    WITH SampleData (PERSON, [COSTCENTER],[AMOUNT], [PAYCODE],[DATE]) AS

    (
    SELECT 101,'123','10.00','REG','04/26/2021'

    UNION ALL SELECT 101,'123','12.00','REG','04/27/2021'

    UNION ALL SELECT 101,'123','08.00','REG','04/28/2021'

    UNION ALL SELECT 101,'456','10.00','REG','04/29/2021'

    UNION ALL SELECT 101,'123','08.00','SICK','04/30/2021'

    UNION ALL SELECT 101,'456','10.00','REG','05/01/2021'

    )

    SELECT * FROM SampleData;

    -- Example Two
    WITH SampleData (PERSON, [COSTCENTER],[AMOUNT], [PAYCODE],[DATE]) AS

    (
    SELECT 101,'123','10.00','REG','04/26/2021'

    UNION ALL SELECT 101,'123','12.00','REG','04/27/2021'

    UNION ALL SELECT 101,'123','08.00','REG','04/28/2021'

    UNION ALL SELECT 101,'456','10.00','REG','04/29/2021'

    UNION ALL SELECT 101,'456','10.00','REG','04/30/2021'

    UNION ALL SELECT 101,'789','12.00','REG','05/01/2021'

    )

    SELECT * FROM SampleData;

    Calc Examples

  • See if this gives you the correct totals.  I have not taken performance into consideration yet, just trying to get the correct result.

    Be sure to add a date check to the WHERE to limit data to the correct week if your actual tables has multiple weeks of data in it.

    ;WITH SampleData (PERSON, [COSTCENTER],[AMOUNT], [PAYCODE],[DATE]) AS
    (
    --1
    SELECT 101,'123',CAST('12.00' AS decimal(5, 2)),'REG','04/26/2021'
    UNION ALL SELECT 101,'123','12.00','REG','04/27/2021'
    UNION ALL SELECT 101,'123','13.00','REG','04/28/2021'
    UNION ALL SELECT 101,'123','13.00','REG','04/29/2021'
    UNION ALL SELECT 101,'123','08.00','SICK','04/30/2021'
    --2
    UNION ALL SELECT 102,'123','10.00','REG','04/26/2021'
    UNION ALL SELECT 102,'123','12.00','REG','04/27/2021'
    UNION ALL SELECT 102,'123','08.00','REG','04/28/2021'
    UNION ALL SELECT 102,'456','10.00','REG','04/29/2021'
    UNION ALL SELECT 102,'123','08.00','SICK','04/30/2021'
    UNION ALL SELECT 102,'456','10.00','REG','05/01/2021'
    --03
    UNION ALL SELECT 103,'123','10.00','REG','04/26/2021'
    UNION ALL SELECT 103,'123','12.00','REG','04/27/2021'
    UNION ALL SELECT 103,'123','08.00','REG','04/28/2021'
    UNION ALL SELECT 103,'456','10.00','REG','04/29/2021'
    UNION ALL SELECT 103,'456','10.00','REG','04/30/2021'
    UNION ALL SELECT 103,'789','12.00','REG','05/01/2021'

    ),
    Totals AS (
    SELECT PERSON, COSTCENTER,
    SUM(CASE WHEN PAYCODE <> 'SICK' THEN AMOUNT ELSE 0.0 END) AS REG,
    SUM(CASE WHEN PAYCODE = 'SICK' THEN AMOUNT ELSE 0.0 END) AS SICK
    FROM SampleData
    GROUP BY PERSON, COSTCENTER WITH ROLLUP
    --ORDER BY PERSON, COSTCENTER
    ) --SELECT * FROM Totals
    SELECT
    T1.PERSON, T1.COSTCENTER,
    CAST(T1.REG / T2.REG * (40 - T2.SICK) AS decimal(5, 2)) AS REG,
    T2.SICK
    FROM Totals T1
    INNER JOIN Totals T2 ON
    T2.PERSON = T1.PERSON AND T2.COSTCENTER IS NULL
    WHERE T1.PERSON IS NOT NULL AND T1.COSTCENTER IS NOT NULL

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • It appears the math in example 3 is off a little.  You could try something like this

    ;with SampleData (person, costcenter, amount, paycode, [date]) as (
    --1
    select 101,'123',cast('12.00' as decimal(5, 2)),'reg','04/26/2021'
    union all select 101,'123','12.00','reg','04/27/2021'
    union all select 101,'123','13.00','reg','04/28/2021'
    union all select 101,'123','13.00','reg','04/29/2021'
    union all select 101,'123','08.00','sick','04/30/2021'
    --2
    union all select 102,'123','10.00','reg','04/26/2021'
    union all select 102,'123','12.00','reg','04/27/2021'
    union all select 102,'123','08.00','reg','04/28/2021'
    union all select 102,'456','10.00','reg','04/29/2021'
    union all select 102,'123','08.00','sick','04/30/2021'
    union all select 102,'456','10.00','reg','05/01/2021'
    --03
    union all select 103,'123','10.00','reg','04/26/2021'
    union all select 103,'123','12.00','reg','04/27/2021'
    union all select 103,'123','08.00','reg','04/28/2021'
    union all select 103,'456','10.00','reg','04/29/2021'
    union all select 103,'456','10.00','reg','04/30/2021'
    union all select 103,'789','12.00','reg','05/01/2021'),
    cc_totals(person, costcenter, paycode, sum_amount) as (
    select person, costcenter, paycode, sum(amount)
    from SampleData
    group by person, costcenter, paycode),
    totals(person, reg_amount, sck_amount) as (
    select person,
    sum(case when paycode='reg' then sum_amount else 0 end),
    sum(case when paycode='sick' then sum_amount else 0 end)
    from cc_totals
    group by person)
    select t1.person, t1.paycode, t1.costcenter,
    case when t1.paycode='reg'
    then (t1.sum_amount/t2.reg_amount)*(40-t2.sck_amount)
    else t2.sck_amount end calc
    from cc_totals t1
    join totals t2 on t1.person = t2.person
    order by t1.person, t1.paycode, t1.costcenter;
    person	paycode	costcenter	calc
    101 reg 123 32.00
    101 sick 123 8.00
    102 reg 123 19.20
    102 reg 456 12.80
    102 sick 123 8.00
    103 reg 123 19.35
    103 reg 456 12.90
    103 reg 789 7.74

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Hi Steve,

    This is just what I was looking for, man, you are awesome !

    You are right for 3rd example, the math was off but at the end of the day that employee got 40 hours in total which was the main ask.

    A quick question, SICK is considered as non-worked time, a person could have other codes like VACATION etc. along with SICK.

    In that case could we do something like this?

    sum(case when paycode in ('sick','vacation') then sum_amount else 0 end)

    Thanks again !

    DS

     

  • Thank you Scott for the help, this was very close but when I ran it, I got 8 hours of SICK twice for example # 1.

    Thanks again !

    DS

  • Thanks for the reply.  That works or why not just copy what Scott did

            SUM(CASE WHEN PAYCODE <> 'SICK' THEN AMOUNT ELSE 0.0 END) AS REG,
    SUM(CASE WHEN PAYCODE = 'SICK' THEN AMOUNT ELSE 0.0 END) AS SICK

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • DiabloSlayer wrote:

    Thank you Scott for the help, this was very close but when I ran it, I got 8 hours of SICK twice for example # 1.

    Thanks again !

    DS

    Oops, you're quite right!  I had one  wrong column reference in my final SELECT.  The last "T2.SICK" should have been "T1.SICK", like so:

    ;WITH SampleData (PERSON, [COSTCENTER],[AMOUNT], [PAYCODE],[DATE]) AS
    (
    --1
    SELECT 101,'123',CAST('12.00' AS decimal(5, 2)),'REG','04/26/2021'
    UNION ALL SELECT 101,'123','12.00','REG','04/27/2021'
    UNION ALL SELECT 101,'123','13.00','REG','04/28/2021'
    UNION ALL SELECT 101,'123','13.00','REG','04/29/2021'
    UNION ALL SELECT 101,'123','08.00','SICK','04/30/2021'
    --2
    UNION ALL SELECT 102,'123','10.00','REG','04/26/2021'
    UNION ALL SELECT 102,'123','12.00','REG','04/27/2021'
    UNION ALL SELECT 102,'123','08.00','REG','04/28/2021'
    UNION ALL SELECT 102,'456','10.00','REG','04/29/2021'
    UNION ALL SELECT 102,'123','08.00','SICK','04/30/2021'
    UNION ALL SELECT 102,'456','10.00','REG','05/01/2021'
    --03
    UNION ALL SELECT 103,'123','10.00','REG','04/26/2021'
    UNION ALL SELECT 103,'123','12.00','REG','04/27/2021'
    UNION ALL SELECT 103,'123','08.00','REG','04/28/2021'
    UNION ALL SELECT 103,'456','10.00','REG','04/29/2021'
    UNION ALL SELECT 103,'456','10.00','REG','04/30/2021'
    UNION ALL SELECT 103,'789','12.00','REG','05/01/2021'

    ),
    Totals AS (
    SELECT PERSON, COSTCENTER,
    SUM(CASE WHEN PAYCODE <> 'SICK' THEN AMOUNT ELSE 0.0 END) AS REG,
    SUM(CASE WHEN PAYCODE = 'SICK' THEN AMOUNT ELSE 0.0 END) AS SICK
    FROM SampleData
    GROUP BY PERSON, COSTCENTER WITH ROLLUP
    --ORDER BY PERSON, COSTCENTER
    ) --SELECT * FROM Totals
    SELECT
    T1.PERSON, T1.COSTCENTER,
    CAST(T1.REG / T2.REG * (40 - T2.SICK) AS decimal(5, 2)) AS REG,
    T1.SICK --<<--!!CORRECTION MADE HERE!!
    FROM Totals T1
    INNER JOIN Totals T2 ON
    T2.PERSON = T1.PERSON AND T2.COSTCENTER IS NULL
    WHERE T1.PERSON IS NOT NULL AND T1.COSTCENTER IS NOT NULL

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Hi Scott,

    Yes sir ! This one works like a charm.

    You made an excellent point about multiple weeks and I thought about it as this could happen in my example since the data pulled is based on bi-weekly time period.

    How would I go about adding a date check in the WHERE clause to limit the data to correct week?

    Thank you once again !

    DS

  • That's easy enough, you can pre-calc a proper date range, using either variables or in a cte.  I'll use variables here just because they're easier to adjust for diff test values while developing.  You can convert to a cte if you prefer that.

    --!!set the work_start_day_of_week to match which day your work week starts!!
    --then set *either* StartDate or EndDate to a date within the starting/ending week:
    --if both are set, then StartDate is used.
    DECLARE @EndDate date
    DECLARE @StartDate date

    DECLARE @work_start_day_of_week smallint = 0 --0=Mon;1=Tue;2=Wed;...;6=Sun.
    --SET @StartDate = GETDATE() --set to ANY day in the FIRST work week
    SET @EndDate = GETDATE() --set to ANY day in the SECOND work week

    IF @StartDate IS NULL
    BEGIN
    SET @EndDate = DATEADD(DAY, -DATEDIFF(DAY, @work_start_day_of_week, @EndDate) % 7 + 6, @EndDate)
    SET @StartDate = DATEADD(DAY, -13, @EndDate)
    END /*IF*/
    ELSE
    BEGIN
    SET @StartDate = DATEADD(DAY, -DATEDIFF(DAY, @work_start_day_of_week, @StartDate) % 7, @StartDate)
    SET @EndDate = DATEADD(DAY, 13, @StartDate)
    END /*ELSE*/

    --...in the main query, add a WHERE condition for date int the "Totals" query
    ...
    Totals AS
    (
    SELECT PERSON, COSTCENTER,
    SUM(CASE WHEN PAYCODE <> 'SICK' THEN AMOUNT ELSE 0.0 END) AS REG,
    SUM(CASE WHEN PAYCODE = 'SICK' THEN AMOUNT ELSE 0.0 END) AS SICK
    FROM SampleData
    WHERE Date BETWEEN @StartDate AND @EndDate --<<--ADD THIS CONDITION
    GROUP BY PERSON, COSTCENTER WITH ROLLUP
    --ORDER BY PERSON, COSTCENTER
    ) --SELECT * FROM Totals
    ...

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Hi Scott,

    This is awesome, I will test run this solution in the morning. Big Thank you again !

    I wanted to ask if it is easy enough to drop the data in the following format? I know Steve's script does that but I already adopted your script and wanted to continue to use it but output the data as shown below.

    The SQL script is all way the at the end.

    Thank you !!

    Calc Examples 2

    Instead of this.

    Calc Examples 3

     

    ;WITH SampleData (PERSON, [COSTCENTER],[AMOUNT], [PAYCODE],[DATE]) AS
    (
    SELECT 101,'123',CAST('12.00' AS decimal(5, 2)),'REG','04/26/2021'
    UNION ALL SELECT 101,'123','12.00','REG','04/27/2021'
    UNION ALL SELECT 101,'123','13.00','REG','04/28/2021'
    UNION ALL SELECT 101,'123','08.00','REG','04/29/2021'
    UNION ALL SELECT 101,'123','08.00','SICK','04/30/2021'

    ),
    Totals AS (
    SELECT PERSON, COSTCENTER,
    SUM(CASE WHEN PAYCODE NOT IN ('SICK') THEN AMOUNT ELSE 0.0 END) AS REG,
    SUM(CASE WHEN PAYCODE IN ('SICK') THEN AMOUNT ELSE 0.0 END) AS SICK
    FROM SampleData
    GROUP BY PERSON, COSTCENTER WITH ROLLUP
    )
    SELECT
    T1.PERSON, T1.COSTCENTER,
    CAST(T1.REG / T2.REG * (40 - T2.SICK) AS decimal(5, 2)) AS REG, T1.SICK
    FROM Totals T1
    INNER JOIN Totals T2 ON
    T2.PERSON = T1.PERSON AND T2.COSTCENTER IS NULL
    WHERE T1.PERSON IS NOT NULL AND T1.COSTCENTER IS NOT NULL
  • Hello Friends,

    So more logic changes requested by the client.

    Similar query but luckily we now have a week indicator with extra columns and new paycodes that should be left out of the calculations as shown in example below.

    Once again thank you for all and any help !

    DS

    ;WITH SampleData (PERSON, [COSTCENTER],[AMOUNT], [PAYCODE],[DATE],FTYPE,FCODE,TEST3,WEEKINDICATOR) AS
    (
    -- Week1
    SELECT 101,'123',CAST('13.00' AS decimal(5, 2)),'REG','04/26/2021','R','R','AB3','1'
    UNION ALL SELECT 101,'123','12.00','REG','04/27/2021','R','R','AB3','1'
    UNION ALL SELECT 101,'123','13.00','REG','04/28/2021','R','R','AB3','1'
    UNION ALL SELECT 101,'123','12.00','REG','04/29/2021','R','R','AB3','1'
    UNION ALL SELECT 101,'123','08.00','SICK','04/30/2021','H','S','AB3','1'
    UNION ALL SELECT 101,'123','58.00','ALL','04/30/2021','','','AB3','1'

    -- Week2
    UNION ALL SELECT 101,'123','13.00','REG','04/26/2021','R','R','AB3','2'
    UNION ALL SELECT 101,'123','12.00','REG','04/27/2021','R','R','AB3','2'
    UNION ALL SELECT 101,'456','13.00','REG','04/28/2021','R','R','AB3','2'
    UNION ALL SELECT 101,'456','12.00','REG','04/29/2021','R','R','AB3','2'
    UNION ALL SELECT 101,'123','04.00','SICK','04/30/2021','H','S','AB3','2'
    UNION ALL SELECT 101,'123','54.00','ALL','04/30/2021','','','AB3','2'
    )
    SELECT * FROM SampleData;

    Expected results

    Calc Examples 5

  • Hi Scott,

    Would you be able to help out again with the latest and hopefully last change request that I posted earlier?

    I've been trying but not able to get the desired results.

    Thank you !

    DS

  • DiabloSlayer wrote:

    So more logic changes requested by the client.

    Similar query but luckily we now have a week indicator with extra columns and new paycodes that should be left out of the calculations as shown in example below.

    I'll need direct explanations and examples of the codes and related calcs.  I'm not going to go thru all the results and figure out what values are included and what's not.  Keep in mind, I'm a volunteer here, to provide SQL expertise, but I'm not spending hours just to figure out what the rules/calcs are from raw result numbers.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Hi Scott,

    I really appreciate your time and help and totally understand that you doing this to help others.

    I had provided the data in previous post (shared below).

    If I can understand how to separate out both weeks (1 and 2) in the same CTE by keeping the same rules as below I will be good to go.

    For each week, the calculation for hours is: total no. of REG hours in a Cost Center Divided By Total REG Hours regardless of Cost Center  times (40 minus (SICK or VAC) hours).

    In the example below for Week1, the REG hours are 50 with 8 SICK hours.

    REG hours in Cost Center 123 = 50 hours

    SICK hours regardless of Cost Center = 8 hours

    So it would be REG hours  = 50/50 x (40 -8) = 32 Hours

    Then SICK hours = 8, so the total hours sent that week = 40

    For Week2, the total REG hours are 50 with 4 SICK hours.

    REG hours in Cost Center 123 = 25 hours

    REG hours in Cost Center 456 = 25 hours

    So it would be REG hours  in Cost Center 123 = 25/50 x (40 -4) = 18 Hours

    And REG hours  in Cost Center 456 = 25/50 x (40 -4) = 18 Hours

    All these three transactions will make it 40 hours

    Paycode ALL should be left alone as is.

    Expected results screenshot is below.

    Once again I really appreciate all and any help !

    Thank you and god bless !

    DS

    ;WITH SampleData (PERSON, [COSTCENTER],[AMOUNT], [PAYCODE],[DATE],FTYPE,FCODE,TEST3,WEEKINDICATOR) AS
    (
    -- Week1
    SELECT 101,'123',CAST('13.00' AS decimal(5, 2)),'REG','04/26/2021','R','R','AB3','1'
    UNION ALL SELECT 101,'123','12.00','REG','04/27/2021','R','R','AB3','1'
    UNION ALL SELECT 101,'123','13.00','REG','04/28/2021','R','R','AB3','1'
    UNION ALL SELECT 101,'123','12.00','REG','04/29/2021','R','R','AB3','1'
    UNION ALL SELECT 101,'123','08.00','SICK','04/30/2021','H','S','AB3','1'
    UNION ALL SELECT 101,'123','58.00','ALL','04/30/2021','','','AB3','1'

    -- Week2
    UNION ALL SELECT 101,'123','13.00','REG','04/26/2021','R','R','AB3','2'
    UNION ALL SELECT 101,'123','12.00','REG','04/27/2021','R','R','AB3','2'
    UNION ALL SELECT 101,'456','13.00','REG','04/28/2021','R','R','AB3','2'
    UNION ALL SELECT 101,'456','12.00','REG','04/29/2021','R','R','AB3','2'
    UNION ALL SELECT 101,'123','04.00','SICK','04/30/2021','H','S','AB3','2'
    UNION ALL SELECT 101,'123','54.00','ALL','04/30/2021','','','AB3','2'
    )
    SELECT * FROM SampleData;

    Calc Examples 5

  • Very busy right now, as soon as I get time, I'll follow up on this ... unless, of course, someone else has already jumped in and solved it.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply