15,305,480 members
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

## Solution 1

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