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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • 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

    personpaycodecostcenteramountweekindicator
    101ALL123 54.002
    101REG123 18.002
    101SICK123 4.002
    101REG456 18.002

    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

    PERSONCOSTCENTERAMOUNTPAYCODEWEEKINDICATOR
    10112332.00REG1
    1011238.00SICK1
    10112358.00ALL1
    10112318.00REG2
    10145618.00REG2
    1011234.00SICK2
    10112354.00ALL2

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

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