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

  • Hello Scott,

    Once again, really appreciate the help. I've been working on it from my end by using different methods using my limited knowledge but no luck.

    No one else has provided any solutions so I will wait whenever you find the time, meanwhile I will continue with my own tries.

    Thank you and god bless !

    DS

  • Some of the amounts in the original didn't seem to add up, and the rules weren't specific for FTYPE and FCODE, but here's my best guess so far:

    ;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'
    ),
    Totals AS (
    SELECT
    PERSON, COSTCENTER, PAYCODE,
    FTYPE, FCODE, TEST3, WEEKINDICATOR,
    SUM(AMOUNT) AS AMOUNT
    FROM SampleData
    GROUP BY
    PERSON, COSTCENTER, PAYCODE, FTYPE, FCODE, TEST3, WEEKINDICATOR
    )
    SELECT
    T1.PERSON, T1.COSTCENTER,
    CASE WHEN T1.PAYCODE IN ('REG') THEN CASE WHEN T1.AMOUNT > 40.0 THEN 40.0 ELSE T1.AMOUNT END - ISNULL(T2.AMOUNT, 0)
    ELSE T1.AMOUNT END AS AMOUNT,
    T1.PAYCODE, T1.FTYPE, T1.FCODE, T1.TEST3, T1.WEEKINDICATOR
    FROM Totals T1
    LEFT OUTER JOIN Totals T2 ON T2.FTYPE = 'H' AND T2.PERSON = T1.PERSON AND
    T2.COSTCENTER = T1.COSTCENTER AND
    T2.TEST3 = T1.TEST3 AND T2.WEEKINDICATOR = T1.WEEKINDICATOR
    ORDER BY T1.PERSON, T1.WEEKINDICATOR, CASE WHEN T1.PAYCODE = 'ALL' THEN 1 ELSE 0 END, PAYCODE

    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 looks pretty close but missing right amount for second row of Reg in Week 2.

    This client is driving me nuts, I've tried the following SQL (using Steven's query) and get exactly what I need for Week 1 or Week2 depending on the where clause. If I can only figure how to fit both weeks in the same CTE using the calculation used below for the other week I will be good to go.

    My issue is that I just don't know how to include other week  in same CTE.

    I have removed all the other non-essential data such FTYPE,FCODE,TEST3 to keep it clean and simple.

    ;WITH SampleData (PERSON, [COSTCENTER],[AMOUNT], [PAYCODE],[DATE],WEEKINDICATOR) AS
    (
    -- Week1
    SELECT 101,'123',CAST('55.00' AS decimal(5, 2)),'REG','04/26/2021','1'
    UNION ALL SELECT 101,'123','08.00','SICK','04/30/2021','1'
    UNION ALL SELECT 101,'123','58.00','ALL','04/30/2021','1'

    -- Week2
    UNION ALL SELECT 101,'123','25.00','REG','04/26/2021','2'
    UNION ALL SELECT 101,'456','25.00','REG','04/29/2021','2'
    UNION ALL SELECT 101,'123','04.00','SICK','04/30/2021','2'
    UNION ALL SELECT 101,'123','54.00','ALL','04/30/2021','2'
    ),
    cc_totals(person, costcenter, paycode, sum_amount,weekindicator) as (
    select person, costcenter, paycode, sum(amount),WEEKINDICATOR
    from SampleData where WEEKINDICATOR = 2
    group by person, costcenter, paycode,weekindicator),
    totals(person, reg_amount, sck_amount, other_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),
    sum(case when paycode ='ALL' 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)
    when t1.paycode = 'ALL' then t2.other_amount
    else t2.sck_amount end amount,weekindicator
    from cc_totals t1
    join totals t2 on t1.person = t2.person

    This gives me the following results. Here, I used a '2' for WEEKINDICATOR in where clause

    person	paycode	costcenter	amount	weekindicator
    101 ALL 123 54.00 2
    101 REG 123 18.00 2
    101 SICK 123 4.00 2
    101 REG 456 18.00 2

    What I want is the following.  The calculation is still the same. If you can help me figure out how to add another week into it it would be a tremendous help and I will appreciate it extremely. Once again, I know you are super busy !

    Thanks a million !

    DS

    PERSON	COSTCENTER	AMOUNT	PAYCODE	WEEKINDICATOR
    101 123 32.00 REG 1
    101 123 8.00 SICK 1
    101 123 58.00 ALL 1
    101 123 18.00 REG 2
    101 456 18.00 REG 2
    101 123 4.00 SICK 2
    101 123 54.00 ALL 2

Viewing 3 posts - 16 through 18 (of 18 total)

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