Sql 2016 - need help with using calculation for paycodes and combining week 1 & 2 records

Question

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 11-May-21 22:45pm

DiabloSlayer2021

Updated 12-May-21 2:42am

Maciej Los

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Answer 1 · 2021-05-12T02:03:00

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