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

Question

Post reply

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

DiabloSlayer

SSCrazy

Points: 2232
More actions
May 9, 2021 at 8:01 am

#3881090
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;
```

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

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

Site Owners SSC Guru Points: 80330 More actions · Answer 1

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

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