Need help with the query
I have a table like below (This is trimmed version, this table contains 11-12 Billion rows with 25 years worth of data. Need to sum gw based on pType as category and separated by combination of eid and cid
eid cid ID pDate pFreq gw PHrs pType
637 163 2037 1/8/21 1 8.13 NULL S
637 163 2037 1/8/21 1 162.5 NULL V
228 787 2037 1/8/21 1 8.13 NULL S
228 787 2037 1/8/21 1 162.5 NULL V
637 163 2037 1/8/21 1 474.5 NULL R
228 787 2037 1/8/21 1 474.5 NULL R
637 163 2037 1/8/21 1 130 NULL H
228 787 2037 1/8/21 1 130 NULL H
637 163 2037 1/15/21 1 602.88 NULL R
228 787 2037 1/15/21 1 602.88 NULL R
637 163 2037 1/22/21 1 32.5 NULL V
228 787 2037 1/22/21 1 619.13 NULL R
637 163 2037 1/22/21 1 619.13 NULL R
228 787 2037 1/22/21 1 32.5 NULL V
228 787 2037 1/27/21 1 300 NULL B
637 163 2037 1/27/21 1 300 NULL B
637 163 2037 1/29/21 1 48.75 NULL V
228 787 2037 1/29/21 1 48.75 NULL V
637 163 2037 1/29/21 1 248.14 NULL O
637 163 2037 1/29/21 1 425.26 NULL R
228 787 2037 1/29/21 1 248.14 NULL O
228 787 2037 1/29/21 1 425.26 NULL R
637 163 2037 1/29/21 1 130 NULL H
228 787 2037 1/29/21 1 130 NULL H
637 163 2037 2/5/21 1 65 NULL S
228 787 2037 2/5/21 1 65 NULL S
637 163 2037 2/5/21 1 587.11 NULL R
228 787 2037 2/5/21 1 587.11 NULL R
228 787 2037 2/12/21 1 617.99 NULL R
637 163 2037 2/12/21 1 617.99 NULL R
637 163 2037 2/19/21 1 635.7 NULL R
228 787 2037 2/19/21 1 635.7 NULL R
637 163 2037 2/26/21 1 654.06 NULL R
228 787 2037 2/26/21 1 654.06 NULL R
637 163 2037 3/5/21 1 162.5 NULL V
228 787 2037 3/5/21 1 489.45 NULL R
637 163 2037 3/5/21 1 489.45 NULL R
228 787 2037 3/5/21 1 162.5 NULL V
637 163 2037 3/12/21 1 24.38 NULL O
637 163 2037 3/12/21 1 650 NULL R
228 787 2037 3/12/21 1 24.38 NULL O
228 787 2037 3/12/21 1 650 NULL R
637 163 2037 3/19/21 1 614.74 NULL R
228 787 2037 3/19/21 1 614.74 NULL R
637 163 2037 3/19/21 1 48.75 NULL S
228 787 2037 3/19/21 1 48.75 NULL S
228 787 2037 3/26/21 1 100.43 NULL O
228 787 2037 3/26/21 1 650 NULL R
637 163 2037 3/26/21 1 100.43 NULL O
637 163 2037 3/26/21 1 650 NULL R
when i run the below query,
> SELECT m.eid,m.cid,m.id,p.pDate,p.pFreq,p.PHrs, SUM(CASE WHEN p.pType
> IN ('H','N','R','S','V') THEN p.gw ELSE 0 END) AS gw_r, SUM(CASE WHEN
> p.pType IN ('B','I','') THEN p.gw ELSE 0 END) AS gw_b, SUM(CASE WHEN
> p.pType IN ('O') THEN p.gw ELSE 0 END) AS gw_ot, SUM(CASE WHEN p.pType
> IN ('C','D','E','K','M','P','T','UP','US','UV','W') THEN p.gross_wages
> ELSE 0 END) AS gw_o FROM [dbo].[mup] m JOIN ccc.dbo.ppart p on
> m.id=p.id JOIN ccc.dbo.cli cli on m.cid=cli.cid JOIN CCC.dbo.cldt
> cd on m.cid=cd.cid
> where p.pDate BETWEEN '01/01/2021' AND '06/30/2021' and p.id=2037 GROUP BY m.eid,m.cid,m.location,m.id,p.pDate,p.pFreq,p.PHrs ORDER BY
> p.pDate
Output returning as
eid cid ID pDate pFreq PHrs gw_r gw_b gw_ot gw_o
637 163 2037 1/8/21 1 NULL 775.13 0 0 0
228 787 2037 1/8/21 1 NULL 775.13 0 0 0
228 787 2037 1/15/21 1 NULL 602.88 0 0 0
637 163 2037 1/15/21 1 NULL 602.88 0 0 0
228 787 2037 1/22/21 1 NULL 651.63 0 0 0
637 163 2037 1/22/21 1 NULL 651.63 0 0 0
228 787 2037 1/27/21 1 NULL 0 300 0 0
637 163 2037 1/27/21 1 NULL 0 300 0 0
637 163 2037 1/29/21 1 NULL 604.01 0 248.14 0
228 787 2037 1/29/21 1 NULL 604.01 0 248.14 0
637 163 2037 2/5/21 1 NULL 652.11 0 0 0
228 787 2037 2/5/21 1 NULL 652.11 0 0 0
637 163 2037 2/12/21 1 NULL 617.99 0 0 0
228 787 2037 2/12/21 1 NULL 617.99 0 0 0
637 163 2037 2/19/21 1 NULL 635.7 0 0 0
228 787 2037 2/19/21 1 NULL 635.7 0 0 0
228 787 2037 2/26/21 1 NULL 654.06 0 0 0
637 163 2037 2/26/21 1 NULL 654.06 0 0 0
228 787 2037 3/5/21 1 NULL 651.95 0 0 0
637 163 2037 3/5/21 1 NULL 651.95 0 0 0
637 163 2037 3/12/21 1 NULL 650 0 24.38 0
228 787 2037 3/12/21 1 NULL 650 0 24.38 0
637 163 2037 3/19/21 1 NULL 663.49 0 0 0
228 787 2037 3/19/21 1 NULL 663.49 0 0 0
637 163 2037 3/26/21 1 NULL 650 0 100.43 0
228 787 2037 3/26/21 1 NULL 650 0 100.43 0
Issue is: gw_r, gw_b, gw_ot, gw_o is giving me duplicate results. How do i fix this? Couldn't figure-out a way to fix this
Appreciate all the help/suggestions
Thank you, Yum Vee
from Recent Questions - Stack Overflow https://ift.tt/3qzjGg4
https://ift.tt/eA8V8J
No comments:
Post a Comment