I'm attempting to consolidate some Time off requests from multiple rows into one row which identifies the start and end time. The current table looks similar to the following;
| ID | hours per day | Submitted Date | Requested Date | Requested Status |
|---|---|---|---|---|
| 'Joe' | 8 | '2017-12-01' | '2017-12-05' | 'Approved' |
| 'Joe' | 8 | '2017-12-01' | '2017-12-06' | 'Approved' |
| 'Joe' | 4 | '2017-12-01' | '2017-12-07' | 'Declined' |
| 'suzie' | 8 | '2018-09-08' | '2017-12-24' | 'Approved' |
| 'suzie' | 8 | '2018-09-08' | '2017-12-25' | 'Approved' |
| 'Joe' | 2 | '2017-12-01' | '2017-12-7' | 'Approved' |
I'm trying to condense this into the following:
| ID | Total Hours | Submitted Date | Requested Date Start | Requested Date End | Requested Status |
|---|---|---|---|---|---|
| Joe | 16 | '2017-12-01' | '2017-12-05' | '2017-12-06' | Approved |
| Joe | 4 | '2017-12-01' | '2017-12-07' | '2017-12-07' | Declined |
| suzie | 16 | '2018-09-08' | '2017-12-24' | '2017-12-25' | Approved |
| Joe | 2 | '2017-12-01' | '2017-12-07' | '2017-12-07' | Approved |
I've tried dozens of things utilizing partitions but i'm not really getting anywhere. Any suggestions?
CodePudding user response：
This is a gaps and islands problem. Assuming you are using MySQL 8 we can use the difference in row numbers method here:
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY RequestedDate) rn1,
ROW_NUMBER() OVER (PARTITION BY ID, RequestedStatus
ORDER BY RequestedDate) rn2
FROM yourTable
)
SELECT ID, SUM(hoursperday) AS total_hours, SubmittedDate,
MIN(RequestedDate) AS rd_start, MAX(RequestedDate) AS rd_end,
RequestedStatus
FROM cte
GROUP BY ID, SubmittedDate, RequestedStatus, rn1 - rn2
ORDER BY ID, RequestedStatus, MIN(RequestedDate);
