May 10, 2021 at 5:53 pm
Hello Scott,
Once again, really appreciate the help. I've been working on it from my end by using different methods using my limited knowledge but no luck.
No one else has provided any solutions so I will wait whenever you find the time, meanwhile I will continue with my own tries.
Thank you and god bless !
DS
May 10, 2021 at 7:14 pm
Some of the amounts in the original didn't seem to add up, and the rules weren't specific for FTYPE and FCODE, but here's my best guess so far:
;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'
),
Totals AS (
SELECT
PERSON, COSTCENTER, PAYCODE,
FTYPE, FCODE, TEST3, WEEKINDICATOR,
SUM(AMOUNT) AS AMOUNT
FROM SampleData
GROUP BY
PERSON, COSTCENTER, PAYCODE, FTYPE, FCODE, TEST3, WEEKINDICATOR
)
SELECT
T1.PERSON, T1.COSTCENTER,
CASE WHEN T1.PAYCODE IN ('REG') THEN CASE WHEN T1.AMOUNT > 40.0 THEN 40.0 ELSE T1.AMOUNT END - ISNULL(T2.AMOUNT, 0)
ELSE T1.AMOUNT END AS AMOUNT,
T1.PAYCODE, T1.FTYPE, T1.FCODE, T1.TEST3, T1.WEEKINDICATOR
FROM Totals T1
LEFT OUTER JOIN Totals T2 ON T2.FTYPE = 'H' AND T2.PERSON = T1.PERSON AND
T2.COSTCENTER = T1.COSTCENTER AND
T2.TEST3 = T1.TEST3 AND T2.WEEKINDICATOR = T1.WEEKINDICATOR
ORDER BY T1.PERSON, T1.WEEKINDICATOR, CASE WHEN T1.PAYCODE = 'ALL' THEN 1 ELSE 0 END, PAYCODE
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 11, 2021 at 7:42 am
Hi Scott,
This looks pretty close but missing right amount for second row of Reg in Week 2.
This client is driving me nuts, I've tried the following SQL (using Steven's query) and get exactly what I need for Week 1 or Week2 depending on the where clause. If I can only figure how to fit both weeks in the same CTE using the calculation used below for the other week I will be good to go.
My issue is that I just don't know how to include other week in same CTE.
I have removed all the other non-essential data such FTYPE,FCODE,TEST3 to keep it clean and simple.
;WITH SampleData (PERSON, [COSTCENTER],[AMOUNT], [PAYCODE],[DATE],WEEKINDICATOR) AS
(
-- Week1
SELECT 101,'123',CAST('55.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,weekindicator) as (
select person, costcenter, paycode, sum(amount),WEEKINDICATOR
from SampleData where WEEKINDICATOR = 2
group by person, costcenter, paycode,weekindicator),
totals(person, reg_amount, sck_amount, other_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),
sum(case when paycode ='ALL' 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)
when t1.paycode = 'ALL' then t2.other_amount
else t2.sck_amount end amount,weekindicator
from cc_totals t1
join totals t2 on t1.person = t2.person
This gives me the following results. Here, I used a '2' for WEEKINDICATOR in where clause
personpaycodecostcenteramountweekindicator
101ALL123 54.002
101REG123 18.002
101SICK123 4.002
101REG456 18.002
What I want is the following. The calculation is still the same. If you can help me figure out how to add another week into it it would be a tremendous help and I will appreciate it extremely. Once again, I know you are super busy !
Thanks a million !
DS
PERSONCOSTCENTERAMOUNTPAYCODEWEEKINDICATOR
10112332.00REG1
1011238.00SICK1
10112358.00ALL1
10112318.00REG2
10145618.00REG2
1011234.00SICK2
10112354.00ALL2
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply