Group By - Workaround - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Group By - Workaround

Author

Topic

joesmithf1
Starting Member

6 Posts

Posted - 2015-03-20 : 11:03:49

Hi,

I have the following SELECT, but i am still getting duplicates because as you know, i can't get PAY_EVT_BGN_DT out of the GROUP BY line. What are my options?

Summary of what i want from the SELECT: each pay period, some employees will have cancel checks, replacement checks, etc. Within these check there could be a range of dates(PAY_EVT_BGN_DT). But i want to specify the pay period begin date, end date, and payday date, hence you see the second CASE statement. However, when i GROUP BY, i want to group by these specified dates, not the range of dates from PAY_EVT_BGN_DT.

Select
CASE when pdetl.PAY_EVT_BGN_DT >= TO_DATE ('01/23/2015', 'mm/dd/yyyy') and pdetl.PAY_EVT_BGN_DT <= TO_DATE ('02/05/2015', 'mm/dd/yyyy') THEN 'A' ELSE 'C' END as Action_Code,

CASE
when pdetl.PAY_EVT_BGN_DT >= TO_DATE ('12/12/2014', 'mm/dd/yyyy') and pdetl.PAY_EVT_BGN_DT <= TO_DATE ('12/25/2014', 'mm/dd/yyyy') THEN '12/12/2014'
when pdetl.PAY_EVT_BGN_DT >= TO_DATE ('12/26/2014', 'mm/dd/yyyy') and pdetl.PAY_EVT_BGN_DT <= TO_DATE ('01/08/2015', 'mm/dd/yyyy') THEN '12/26/2014'
when pdetl.PAY_EVT_BGN_DT >= TO_DATE ('01/09/2015', 'mm/dd/yyyy') and pdetl.PAY_EVT_BGN_DT <= TO_DATE ('01/22/2015', 'mm/dd/yyyy') THEN '01/09/2015'
end as PP_Start_Date,

trim(TO_CHAR(SUM(PDETL.INPUT_AM)/60,'9999D99')),
EMPLOYEE_ID,
PDETL.EVNT_TYP_CD
FROM O_HRUSR.PAY_DETL PDETL
LEFT JOIN hrintrface.oc_bo_dw_rext2 rext2
ON pdetl.INTERNAL_EMPL_ID = rext2.internal_empl_id
AND pdetl.APPOINTMENT_ID = rext2.APPOINTMENT_ID

WHERE CHK_DT >= TO_DATE ('01/31/2015', 'mm/dd/yyyy') and CHK_DT <= TO_DATE ('02/13/2015', 'mm/dd/yyyy')
AND PDETL.EVNT_TYP_CD in('SICKPAY','VACPAY','REGPAY')
GROUP BY EMPLOYEE_ID,PDETL.EVNT_TYP_CD,PAY_EVT_BGN_DT

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-20 : 11:46:35

I think you might be using Oracle (but this is a SQL server forum). In any case, the solution can be as simple as putting the case logic in a subquery then doing the aggregation in outer query

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources