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

Question

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

DiabloSlayer

SSCrazy

Points: 2232
More actions
May 2, 2021 at 8:33 am

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

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

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

ScottPletcher SSC Guru Points: 101095 More actions · Answer 1

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 NULL

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Steve Collins SSCrazy Points: 2157 More actions · Answer 2

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

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

DiabloSlayer SSCrazy Points: 2232 More actions · Answer 3

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

DiabloSlayer SSCrazy Points: 2232 More actions · Answer 4

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

Steve Collins SSCrazy Points: 2157 More actions · Answer 5

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

ScottPletcher SSC Guru Points: 101095 More actions · Answer 6

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 NULL

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

DiabloSlayer SSCrazy Points: 2232 More actions · Answer 7

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

ScottPletcher SSC Guru Points: 101095 More actions · Answer 8

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

DiabloSlayer SSCrazy Points: 2232 More actions · Answer 9

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

Calc Examples 2

Instead of this.

Calc Examples 3

;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

DiabloSlayer SSCrazy Points: 2232 More actions · Answer 10

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

Calc Examples 5

DiabloSlayer SSCrazy Points: 2232 More actions · Answer 11

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

ScottPletcher SSC Guru Points: 101095 More actions · Answer 12

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

DiabloSlayer SSCrazy Points: 2232 More actions · Answer 13

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;

Calc Examples 5

ScottPletcher SSC Guru Points: 101095 More actions · Answer 14

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".