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

• Hello Friends,

I 'm in need of some SQL help please, I have SQL examples below with the following conditions:

All REG plus SICK hours cannot exceed 40 hours in that week.

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 hours)

In first example below, 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

Second SQL example below, the REG hours are 50 with 8 SICK hours.

REG hours in Cost Center 123 = 30 hours

REG hours in Cost Center 456 = 20 hours

SICK hours regardless of Cost Center = 8 hours

So it would be REG hours  in Cost Center 123 = 30/50 x (40 -8) = 19.2 Hours

And REG hours  in Cost Center 456 = 20/50 x (40 -8) = 12.8 Hours

And SICK hours  = 8 hours, all these three transactions will make it 40 hours

3rd and last example below, the REG hours are 62.

REG hours in Cost Center 123 = 30 hours

REG hours in Cost Center 456 = 20 hours

REG hours in Cost Center 789 = 12 hours

So it would be REG hours  in Cost Center 123 = 30/62 x (40 -0) = 19.2 Hours

And REG hours  in Cost Center 456 = 20/62 x (40 -0) = 12.8 Hours

And REG hours  in Cost Center 789 = 12/62 x (40 -0) = 8 Hours, all these three transactions will make it 40 hours

Thank you all in advance and god bless !

`-- Example OneWITH SampleData (PERSON, [COSTCENTER],[AMOUNT], [PAYCODE],[DATE]) AS(    SELECT 101,'123','12.00','REG','04/26/2021'    UNION ALL SELECT 101,'123','12.00','REG','04/27/2021'    UNION ALL SELECT 101,'123','13.00','REG','04/28/2021'    UNION ALL SELECT 101,'123','13.00','REG','04/29/2021'    UNION ALL SELECT 101,'123','08.00','SICK','04/30/2021'   )SELECT * FROM SampleData;-- Example TwoWITH SampleData (PERSON, [COSTCENTER],[AMOUNT], [PAYCODE],[DATE]) AS(    SELECT 101,'123','10.00','REG','04/26/2021'    UNION ALL SELECT 101,'123','12.00','REG','04/27/2021'    UNION ALL SELECT 101,'123','08.00','REG','04/28/2021'    UNION ALL SELECT 101,'456','10.00','REG','04/29/2021'    UNION ALL SELECT 101,'123','08.00','SICK','04/30/2021'    UNION ALL SELECT 101,'456','10.00','REG','05/01/2021'   )SELECT * FROM SampleData;-- Example TwoWITH SampleData (PERSON, [COSTCENTER],[AMOUNT], [PAYCODE],[DATE]) AS(    SELECT 101,'123','10.00','REG','04/26/2021'    UNION ALL SELECT 101,'123','12.00','REG','04/27/2021'    UNION ALL SELECT 101,'123','08.00','REG','04/28/2021'    UNION ALL SELECT 101,'456','10.00','REG','04/29/2021'    UNION ALL SELECT 101,'456','10.00','REG','04/30/2021'    UNION ALL SELECT 101,'789','12.00','REG','05/01/2021'   )SELECT * FROM SampleData;`

• See if this gives you the correct totals.  I have not taken performance into consideration yet, just trying to get the correct result.

Be sure to add a date check to the WHERE to limit data to the correct week if your actual tables has multiple weeks of data in it.

`;WITH SampleData (PERSON, [COSTCENTER],[AMOUNT], [PAYCODE],[DATE]) AS(    --1    SELECT 101,'123',CAST('12.00' AS decimal(5, 2)),'REG','04/26/2021'    UNION ALL SELECT 101,'123','12.00','REG','04/27/2021'    UNION ALL SELECT 101,'123','13.00','REG','04/28/2021'    UNION ALL SELECT 101,'123','13.00','REG','04/29/2021'    UNION ALL SELECT 101,'123','08.00','SICK','04/30/2021'    --2    UNION ALL SELECT 102,'123','10.00','REG','04/26/2021'    UNION ALL SELECT 102,'123','12.00','REG','04/27/2021'    UNION ALL SELECT 102,'123','08.00','REG','04/28/2021'    UNION ALL SELECT 102,'456','10.00','REG','04/29/2021'    UNION ALL SELECT 102,'123','08.00','SICK','04/30/2021'    UNION ALL SELECT 102,'456','10.00','REG','05/01/2021'    --03    UNION ALL SELECT 103,'123','10.00','REG','04/26/2021'    UNION ALL SELECT 103,'123','12.00','REG','04/27/2021'    UNION ALL SELECT 103,'123','08.00','REG','04/28/2021'    UNION ALL SELECT 103,'456','10.00','REG','04/29/2021'    UNION ALL SELECT 103,'456','10.00','REG','04/30/2021'    UNION ALL SELECT 103,'789','12.00','REG','05/01/2021'  ),Totals AS (    SELECT PERSON, COSTCENTER,         SUM(CASE WHEN PAYCODE <> 'SICK' THEN AMOUNT ELSE 0.0 END) AS REG,        SUM(CASE WHEN PAYCODE = 'SICK'  THEN AMOUNT ELSE 0.0 END) AS SICK    FROM SampleData    GROUP BY PERSON, COSTCENTER WITH ROLLUP    --ORDER BY PERSON, COSTCENTER) --SELECT * FROM TotalsSELECT     T1.PERSON, T1.COSTCENTER,    CAST(T1.REG / T2.REG * (40 - T2.SICK) AS decimal(5, 2)) AS REG,    T2.SICKFROM Totals T1INNER JOIN Totals T2 ON     T2.PERSON = T1.PERSON AND T2.COSTCENTER IS NULLWHERE T1.PERSON IS NOT NULL AND T1.COSTCENTER IS NOT NULL`

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

• It appears the math in example 3 is off a little.  You could try something like this

`;with SampleData (person, costcenter, amount, paycode, [date]) as (    --1    select 101,'123',cast('12.00' as decimal(5, 2)),'reg','04/26/2021'    union all select 101,'123','12.00','reg','04/27/2021'    union all select 101,'123','13.00','reg','04/28/2021'    union all select 101,'123','13.00','reg','04/29/2021'    union all select 101,'123','08.00','sick','04/30/2021'    --2    union all select 102,'123','10.00','reg','04/26/2021'    union all select 102,'123','12.00','reg','04/27/2021'    union all select 102,'123','08.00','reg','04/28/2021'    union all select 102,'456','10.00','reg','04/29/2021'    union all select 102,'123','08.00','sick','04/30/2021'    union all select 102,'456','10.00','reg','05/01/2021'    --03    union all select 103,'123','10.00','reg','04/26/2021'    union all select 103,'123','12.00','reg','04/27/2021'    union all select 103,'123','08.00','reg','04/28/2021'    union all select 103,'456','10.00','reg','04/29/2021'    union all select 103,'456','10.00','reg','04/30/2021'    union all select 103,'789','12.00','reg','05/01/2021'),cc_totals(person, costcenter, paycode, sum_amount) as (    select person, costcenter, paycode, sum(amount)    from SampleData    group by person, costcenter, paycode),totals(person, reg_amount, sck_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)    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)             else t2.sck_amount end calcfrom cc_totals t1     join totals t2 on t1.person = t2.personorder by t1.person, t1.paycode, t1.costcenter;`
`person	paycode	costcenter	calc101	reg	123		32.00101	sick	123		8.00102	reg	123		19.20102	reg	456		12.80102	sick	123		8.00103	reg	123		19.35103	reg	456		12.90103	reg	789		7.74`

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

• Hi Steve,

This is just what I was looking for, man, you are awesome !

You are right for 3rd example, the math was off but at the end of the day that employee got 40 hours in total which was the main ask.

A quick question, SICK is considered as non-worked time, a person could have other codes like VACATION etc. along with SICK.

In that case could we do something like this?

`sum(case when paycode in ('sick','vacation') then sum_amount else 0 end)`

Thanks again !

DS

• Thank you Scott for the help, this was very close but when I ran it, I got 8 hours of SICK twice for example # 1.

Thanks again !

DS

• Thanks for the reply.  That works or why not just copy what Scott did

`        SUM(CASE WHEN PAYCODE <> 'SICK' THEN AMOUNT ELSE 0.0 END) AS REG,        SUM(CASE WHEN PAYCODE = 'SICK'  THEN AMOUNT ELSE 0.0 END) AS SICK`

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

• DiabloSlayer wrote:

Thank you Scott for the help, this was very close but when I ran it, I got 8 hours of SICK twice for example # 1.

Thanks again !

DS

Oops, you're quite right!  I had one  wrong column reference in my final SELECT.  The last "T2.SICK" should have been "T1.SICK", like so:

`;WITH SampleData (PERSON, [COSTCENTER],[AMOUNT], [PAYCODE],[DATE]) AS(    --1    SELECT 101,'123',CAST('12.00' AS decimal(5, 2)),'REG','04/26/2021'    UNION ALL SELECT 101,'123','12.00','REG','04/27/2021'    UNION ALL SELECT 101,'123','13.00','REG','04/28/2021'    UNION ALL SELECT 101,'123','13.00','REG','04/29/2021'    UNION ALL SELECT 101,'123','08.00','SICK','04/30/2021'    --2    UNION ALL SELECT 102,'123','10.00','REG','04/26/2021'    UNION ALL SELECT 102,'123','12.00','REG','04/27/2021'    UNION ALL SELECT 102,'123','08.00','REG','04/28/2021'    UNION ALL SELECT 102,'456','10.00','REG','04/29/2021'    UNION ALL SELECT 102,'123','08.00','SICK','04/30/2021'    UNION ALL SELECT 102,'456','10.00','REG','05/01/2021'    --03    UNION ALL SELECT 103,'123','10.00','REG','04/26/2021'    UNION ALL SELECT 103,'123','12.00','REG','04/27/2021'    UNION ALL SELECT 103,'123','08.00','REG','04/28/2021'    UNION ALL SELECT 103,'456','10.00','REG','04/29/2021'    UNION ALL SELECT 103,'456','10.00','REG','04/30/2021'    UNION ALL SELECT 103,'789','12.00','REG','05/01/2021'  ),Totals AS (    SELECT PERSON, COSTCENTER,         SUM(CASE WHEN PAYCODE <> 'SICK' THEN AMOUNT ELSE 0.0 END) AS REG,        SUM(CASE WHEN PAYCODE = 'SICK'  THEN AMOUNT ELSE 0.0 END) AS SICK    FROM SampleData    GROUP BY PERSON, COSTCENTER WITH ROLLUP    --ORDER BY PERSON, COSTCENTER) --SELECT * FROM TotalsSELECT     T1.PERSON, T1.COSTCENTER,    CAST(T1.REG / T2.REG * (40 - T2.SICK) AS decimal(5, 2)) AS REG,    T1.SICK --<<--!!CORRECTION MADE HERE!!FROM Totals T1INNER JOIN Totals T2 ON     T2.PERSON = T1.PERSON AND T2.COSTCENTER IS NULLWHERE T1.PERSON IS NOT NULL AND T1.COSTCENTER IS NOT NULL`

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,

Yes sir ! This one works like a charm.

You made an excellent point about multiple weeks and I thought about it as this could happen in my example since the data pulled is based on bi-weekly time period.

How would I go about adding a date check in the WHERE clause to limit the data to correct week?

Thank you once again !

DS

• That's easy enough, you can pre-calc a proper date range, using either variables or in a cte.  I'll use variables here just because they're easier to adjust for diff test values while developing.  You can convert to a cte if you prefer that.

`--!!set the work_start_day_of_week to match which day your work week starts!!--then set *either* StartDate or EndDate to a date within the starting/ending week:--if both are set, then StartDate is used.DECLARE @EndDate dateDECLARE @StartDate dateDECLARE @work_start_day_of_week smallint = 0 --0=Mon;1=Tue;2=Wed;...;6=Sun.--SET @StartDate = GETDATE() --set to ANY day in the FIRST work weekSET @EndDate = GETDATE() --set to ANY day in the SECOND work weekIF @StartDate IS NULLBEGIN    SET @EndDate = DATEADD(DAY, -DATEDIFF(DAY, @work_start_day_of_week, @EndDate) % 7 + 6, @EndDate)     SET @StartDate = DATEADD(DAY, -13, @EndDate)END /*IF*/ELSEBEGIN    SET @StartDate = DATEADD(DAY, -DATEDIFF(DAY, @work_start_day_of_week, @StartDate) % 7, @StartDate)     SET @EndDate = DATEADD(DAY, 13, @StartDate)END /*ELSE*/--...in the main query, add a WHERE condition for date int the "Totals" query...Totals AS (    SELECT PERSON, COSTCENTER,         SUM(CASE WHEN PAYCODE <> 'SICK' THEN AMOUNT ELSE 0.0 END) AS REG,        SUM(CASE WHEN PAYCODE = 'SICK'  THEN AMOUNT ELSE 0.0 END) AS SICK    FROM SampleData    WHERE Date BETWEEN @StartDate AND @EndDate --<<--ADD THIS CONDITION    GROUP BY PERSON, COSTCENTER WITH ROLLUP    --ORDER BY PERSON, COSTCENTER) --SELECT * FROM Totals...`

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 is awesome, I will test run this solution in the morning. Big Thank you again !

I wanted to ask if it is easy enough to drop the data in the following format? I know Steve's script does that but I already adopted your script and wanted to continue to use it but output the data as shown below.

The SQL script is all way the at the end.

Thank you !!

`;WITH SampleData (PERSON, [COSTCENTER],[AMOUNT], [PAYCODE],[DATE]) AS(    SELECT 101,'123',CAST('12.00' AS decimal(5, 2)),'REG','04/26/2021'    UNION ALL SELECT 101,'123','12.00','REG','04/27/2021'    UNION ALL SELECT 101,'123','13.00','REG','04/28/2021'    UNION ALL SELECT 101,'123','08.00','REG','04/29/2021'    UNION ALL SELECT 101,'123','08.00','SICK','04/30/2021'  ),Totals AS (    SELECT PERSON, COSTCENTER,         SUM(CASE WHEN PAYCODE NOT IN ('SICK') THEN AMOUNT ELSE 0.0 END) AS REG,        SUM(CASE WHEN PAYCODE IN ('SICK')  THEN AMOUNT ELSE 0.0 END) AS SICK    FROM SampleData    GROUP BY PERSON, COSTCENTER WITH ROLLUP) SELECT     T1.PERSON, T1.COSTCENTER,    CAST(T1.REG / T2.REG * (40 - T2.SICK) AS decimal(5, 2)) AS REG, T1.SICK FROM Totals T1INNER JOIN Totals T2 ON     T2.PERSON = T1.PERSON AND T2.COSTCENTER IS NULLWHERE T1.PERSON IS NOT NULL AND T1.COSTCENTER IS NOT NULL`
• Hello Friends,

So more logic changes requested by the client.

Similar query but luckily we now have a week indicator with extra columns and new paycodes that should be left out of the calculations as shown in example below.

Once again thank you for all and any help !

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

Expected results

• Hi Scott,

Would you be able to help out again with the latest and hopefully last change request that I posted earlier?

I've been trying but not able to get the desired results.

Thank you !

DS

• DiabloSlayer wrote:

So more logic changes requested by the client.

Similar query but luckily we now have a week indicator with extra columns and new paycodes that should be left out of the calculations as shown in example below.

I'll need direct explanations and examples of the codes and related calcs.  I'm not going to go thru all the results and figure out what values are included and what's not.  Keep in mind, I'm a volunteer here, to provide SQL expertise, but I'm not spending hours just to figure out what the rules/calcs are from raw result numbers.

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,

I really appreciate your time and help and totally understand that you doing this to help others.

I had provided the data in previous post (shared below).

If I can understand how to separate out both weeks (1 and 2) in the same CTE by keeping the same rules as below I will be good to go.

For each week, the calculation for hours is: total no. of REG 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.

Once again I really appreciate all and any help !

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

• Very busy right now, as soon as I get time, I'll follow up on this ... unless, of course, someone else has already jumped in and solved it.

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

Viewing 15 posts - 1 through 15 (of 18 total)