SQL 2016 - Need help with complex calculations based on paycodes - New Changes

  • Hello Friends,

    Posting this for help as the client made more requirement changes.

    I have a SQL example below with the following conditions:

    All REG plus (SICK or VAC) hours cannot exceed 40 hours in week 1 or week 2 (there is week indicator).

    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 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.

    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

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

Viewing 2 posts - 1 through 1 (of 1 total)

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