Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Group by two case statement with date

Abhijit2610May 27 2019 — edited May 27 2019

I have to group by date for below query, but REG and OT hours are coming on different row for same date. Can anyone tell me how to achieve this?

SELECT VP.PERSONNUM AS PERSONID,

VP.FIRSTNM AS FIRST_NAME,

VP.LASTNM AS LAST_NAME,

(select personnum from vp_employeev42 where personnum = vp.homelaborlevelnm6) as FOREMAN_CODE,

CA.PERSONCSTMDATATXT as SHIFT,

TO_CHAR(X.APPLYDTM, 'DD-MM-YYYY') AS LABOR_DATE,

VP.HOMELABORLEVELNM3 AS DEPARTMENT,

CASE WHEN TS.LABORLEVELNAME3 != 'NULL' THEN TS.LABORLEVELNAME3 ELSE VP.HOMELABORLEVELNM3 END AS CHGD_DEPARTMENT,

CASE WHEN P.NAME = 'US-Regular' THEN SUM(X.DURATIONSECSQTY/3600.0) ELSE 0 END AS REG,

CASE WHEN P.NAME = 'US-Overtime' THEN SUM(X.DURATIONSECSQTY/3600.0) ELSE 0 END AS OT,

CASE WHEN P.NAME = 'US-Doubletime' THEN SUM(X.DURATIONSECSQTY/3600.0) ELSE 0 END AS DBL,

CASE WHEN TS.LABORLEVELNAME4 != 'NULL' THEN TS.LABORLEVELNAME4 ELSE VP.HOMELABORLEVELNM4 END AS ACCOUNT_ID

FROM VP_EMPLOYEEV42 VP, WFCTOTAL X, PAYCODE P, PERSONCSTMDATA CA, VP_TIMESHTPUNCHV42 TS

WHERE VP.PERSONID = X.EMPLOYEEID AND

X.PAYCODEID = P.PAYCODEID AND

X.EMPLOYEEID = CA.PERSONID AND

CA.CUSTOMDATADEFID ='13' AND

X.TIMESHEETITEMID = TS.TIMESHEETITEMID AND

P.NAME in ('US-Regular','US-Overtime','US-Doubletime') AND

X.EMPLOYEEID in (select personid from PERSONCSTMDATA

where CUSTOMDATADEFID ='14' and PERSONCSTMDATATXT = 'USKEANE') and

VP.PAYRULENAME in ('USKEANE 10 HR Shift','USKEANE 12 HR Shift','USKEANE 1st Shift','USKEANE 2nd Shift','USKEANE 3rd Shift') and

X.APPLYDTM between '01-APR-19' and '30-APR-19'

group by VP.PERSONID, VP.PERSONNUM, VP.FIRSTNM, VP.LASTNM, VP.HOMELABORLEVELNM6, X.APPLYDTM, VP.HOMELABORLEVELNM3,

CA.PERSONCSTMDATATXT ,VP.HOMELABORLEVELNM4, P.NAME, TS.LABORLEVELNAME3, TS.LABORLEVELNAME4

ORDER BY VP.PERSONID, X.APPLYDTM DESC

below is the result of the above query, there are two rows with same date 10-04-2019, so I want to group by that two rows in single row

PERSONIDFIRST_NAMELAST_NAMEFOREMAN_CODESHIFTLABOR_DATEDEPARTMENTCHGD_DEPARTMENTREGOTDBLACCOUNT_ID
E28MariaPutnamE45110-04-2019258552585540060467
E28MariaPutnamE45110-04-2019258552585504060467
E28MariaPutnamE45115-04-2019258552585580060467
E28MariaPutnamE45116-04-2019258552585580060467
This post has been answered by Abhijit2610 on May 27 2019
Jump to Answer
Comments
Post Details
Added on May 27 2019
6 comments
1,663 views