Click here to Skip to main content
15,305,480 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello Friends,

I 'm in need of some SQL help please, I have an SQL example 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 REG hours in a Cost Center Divided By total no of all REG Hours regardless of Cost Center times (40 minus SICK hours)

In the example below:

For WEEK 1 - Total number of REG hours regardless of Cost Centers are 55 hours with 8 SICK hours.

REG hours in Cost Center 123 = 55 hours

SICK hours regardless of Cost Center = 8 hours

So it would be REG hours = 55/55 x (40 -8) = 32 Hours

Then SICK hours = 8, so the total hours sent that week = 40


For WEEK 2 - Total number of REG hours regardless of Cost Centers are 50 hours with 4 SICK hours.

REG hours in Cost Center 123 = 25 hours
So it would be REG hours = 25/50 x (40 -4) = 18 Hours

REG hours in Cost Center 456 = 25 hours
So it would be REG hours = 25/50 x (40 -4) = 18 Hours

Then SICK hours = 4, so the total hours sent that week = 40

What I have tried:

This is what I have tried so far. I can get data right for week1, I just don't know how to add week2 in there to get desired results.

SQL
;WITH SampleData (PERSON, [COSTCENTER],[AMOUNT], [PAYCODE],[DATE],WEEKINDICATOR) AS
(
    -- Week1
    SELECT 101,'123',CAST('13.00' AS decimal(5, 2)),'REG','04/26/2021','1'
    UNION ALL SELECT 101,'123','08.00','SICK','04/30/2021','1'
	UNION ALL SELECT 101,'123','58.00','ALL','04/30/2021','1'
    
	-- Week2
	UNION ALL SELECT 101,'123','25.00','REG','04/26/2021','2'
    UNION ALL SELECT 101,'456','25.00','REG','04/29/2021','2'
    UNION ALL SELECT 101,'123','04.00','SICK','04/30/2021','2'
    UNION ALL SELECT 101,'123','54.00','ALL','04/30/2021','2'
),
cc_totals(person, costcenter, paycode, sum_amount) as (
    select person, costcenter, paycode, sum(amount)
    from SampleData where paycode <> 'ALL' and WEEKINDICATOR = 1
    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 amount
from cc_totals t1
     join totals t2 on t1.person = t2.person
union all 
select person, costcenter, paycode, amount
    from SampleData where paycode = 'ALL' and WEEKINDICATOR = 1
Posted
Updated 12-May-21 2:42am
v2

1 solution

You need to include WEEKINDICATOR in your grouping rather than as part of the WHERE clause.

Your explanation of the calculations doesn't quite match up to your worked example and it's not clear what "ALL" represents, so the following code won't exactly solve your problem. But hopefully it will point you in the right direction.

If you PIVOT[^] your sample data like this
SQL
SELECT PERSON
    ,COSTCENTER
    ,WEEKINDICATOR
    ,isnull([ALL],0) AS [ALL]
    ,isnull([REG],0) AS [REG]
    ,isnull([SICK],0) AS [SICK]
FROM
(
    SELECT PERSON, COSTCENTER, WEEKINDICATOR, PAYCODE, AMOUNT
    FROM SampleData
) src
PIVOT
(
    SUM(AMOUNT) FOR PAYCODE IN ([ALL],[REG],[SICK])
) pvt
Then you get these tabular results
PERSON	COSTCENTER	WEEKINDICATOR	ALL		REG		SICK
101		123			1				58.00	55.00	8.00
101		123			2				54.00	25.00	4.00
101		456			2				0.00	25.00	0.00
Following your example I put that PIVOT query into another CTE which I called PIVOTAL and then queried that data to get the totals for REG ignoring COSTCENTER ...
SQL
SELECT PERSON, COSTCENTER, WEEKINDICATOR, [ALL],[REG],[SICK],
	SUM([REG]) OVER (PARTITION BY PERSON, WEEKINDICATOR ORDER BY PERSON, WEEKINDICATOR) AS TotalREGIgnoreCC
FROM PIVOTAL
order by COSTCENTER, WEEKINDICATOR
Notice the use of OVER - I used a Window Function[^]
That gave me these results
PERSON	COSTCENTER	WEEKINDICATOR	ALL		REG		SICK	TotalREGIgnoreCC
101		123			1				58.00	55.00	8.00	55.00
101		123			2				54.00	25.00	4.00	50.00
101		456			2				0.00	25.00	0.00	50.00
Which you can just query with your calculations in place e.g. (Warning - the calculation here is for demo purposes only - you will need to replace it and you may need to GROUP or filter your results)
SQL
select *, ([REG]/TotalREGIgnoreCC * (40 -SICK)) + SICK as FinalCalc
FROM finally
   
Comments
Maciej Los 12-May-21 8:42am
   
5ed!
DiabloSlayer2021 12-May-21 11:21am
   
Hi CHil60, Thank you for the reply and help. The ALL paycode is something we exclude from the 40 hour calculation and leave it as is.

I guess for some reasons the desired output did not make it in my original post.

PERSON COSTCENTER AMOUNT PAYCODE WEEKINDICATOR
101 123 32.00 REG 1
101 123 8.00 SICK 1
101 123 58.00 ALL 1
101 123 18.00 REG 2
101 456 18.00 REG 2
101 123 4.00 SICK 2
101 123 54.00 ALL 2

I tried grouping of WEEKINDICATOR but it didn't work.

The paycodes, hours etc. has to line up as in the output displayed above.

Please let me know what kind of changes I need to make to get that working.

Thank you,

DS
CHill60 12-May-21 12:24pm
   
Just join that final bit of SQL back to the original data e.g.
select a.PERSON		,a.COSTCENTER	,CASE WHEN b.PAYCODE = 'REG' THEN ([REG]/TotalREGIgnoreCC * (40 -SICK)) + SICK 		ELSE b.[Amount] END AS [AMOUNT]	,b.PAYCODE	,b.WEEKINDICATORFROM finally aINNER JOIN SampleData b on a.PERSON = b.PERSON 		AND a.COSTCENTER = b.COSTCENTER		AND a.WEEKINDICATOR = b.WEEKINDICATOR
As I said in my solution, the calculation I've used is wrong and you will need to fix it to match your rules (which I could not follow)
DiabloSlayer2021 12-May-21 14:18pm
   
Hi Chill60, I'm sure I'm missing something when trying to put different bits of SQL codes together. Would you able to put it in one statement so I can copy and paste it on my end and see the results?

Thank you and god bless !

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900