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
| PERSONID | FIRST_NAME | LAST_NAME | FOREMAN_CODE | SHIFT | LABOR_DATE | DEPARTMENT | CHGD_DEPARTMENT | REG | OT | DBL | ACCOUNT_ID |
|---|
| E28 | Maria | Putnam | E45 | 1 | 10-04-2019 | 25855 | 25855 | 4 | 0 | 0 | 60467 |
| E28 | Maria | Putnam | E45 | 1 | 10-04-2019 | 25855 | 25855 | 0 | 4 | 0 | 60467 |
| E28 | Maria | Putnam | E45 | 1 | 15-04-2019 | 25855 | 25855 | 8 | 0 | 0 | 60467 |
| E28 | Maria | Putnam | E45 | 1 | 16-04-2019 | 25855 | 25855 | 8 | 0 | 0 | 60467 |