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 One
WITH 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 Two
WITH 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 Two
WITH 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 Totals
SELECT
T1.PERSON, T1.COSTCENTER,
CAST(T1.REG / T2.REG * (40 - T2.SICK) AS decimal(5, 2)) AS REG,
T2.SICK
FROM Totals T1
INNER JOIN Totals T2 ON
T2.PERSON = T1.PERSON AND T2.COSTCENTER IS NULL
WHERE T1.PERSON IS NOT NULL AND T1.COSTCENTER IS NOT NULLSQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
-
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 calc
from cc_totals t1
join totals t2 on t1.person = t2.person
order by t1.person, t1.paycode, t1.costcenter;personpaycodecostcentercalc
101reg12332.00
101sick1238.00
102reg12319.20
102reg45612.80
102sick1238.00
103reg12319.35
103reg45612.90
103reg7897.74Aus 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 SICKAus 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 Totals
SELECT
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 T1
INNER JOIN Totals T2 ON
T2.PERSON = T1.PERSON AND T2.COSTCENTER IS NULL
WHERE T1.PERSON IS NOT NULL AND T1.COSTCENTER IS NOT NULLSQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
-
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 date
DECLARE @StartDate date
DECLARE @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 week
SET @EndDate = GETDATE() --set to ANY day in the SECOND work week
IF @StartDate IS NULL
BEGIN
SET @EndDate = DATEADD(DAY, -DATEDIFF(DAY, @work_start_day_of_week, @EndDate) % 7 + 6, @EndDate)
SET @StartDate = DATEADD(DAY, -13, @EndDate)
END /*IF*/
ELSE
BEGIN
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) A socialist is someone who will give you the shirt off *someone else's* back.
-
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 !!
Instead of this.
;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 T1
INNER JOIN Totals T2 ON
T2.PERSON = T1.PERSON AND T2.COSTCENTER IS NULL
WHERE 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) A socialist is someone who will give you the shirt off *someone else's* back.
-
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) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply