# 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;` • 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 2 (of 2 total)