I've hit the wall with this query and i'm asking for any assistance on this one
Background: I need to create a query that identifies all the "Unpaid" work days based on the data that is available in our Oracle database
Our Time & Labour tracking data contains only 1 date column (EFF_DT) representing "work date"
A simplified version of the raw table looks like below:
| EEID | WORK_DT | PAYCODE |
|---|---|---|
| 1111 | 2022/03/01 | UNPAID |
| 1111 | 2022/03/02 | REG_WORK |
| 1111 | 2022/03/03 | REG_WORK |
| 1111 | 2022/03/04 | UNPAID |
| 1111 | 2022/03/05 | UNPAID |
| 1111 | 2022/03/06 | SICK |
| 1111 | 2022/03/07 | SICK |
| 1111 | 2022/03/08 | UNPAID |
| 1111 | 2022/03/09 | UNPAID |
| 1111 | 2022/03/10 | UNPAID |
What I need to do is to filter for only the "UNPAID" rows and then I need to identify the "range" of start/end work dates of the unpaid leave
So the expected results would need to look like the following:
| EEID | START_EFF_DT | END_EFF_DT |
|---|---|---|
| 1111 | 2022/03/01 | 2022/03/01 |
| 1111 | 2022/03/04 | 2022/03/05 |
| 1111 | 2022/03/08 | 2022/03/10 |
How should I approach designing this SQL for the crystal report that needs to be created?
Any help is greatly appreciated!
For users on older versions of Oracle Database (before version 12.1), who cannot use the match_recognize clause, here is a solution using just analytic functions and a very nice method for identifying gaps and islands, known as the "fixed differences" or "tabibito-san" method. The trick is the creation of the additional grouping expressions, labeled grp, in the helper subquery (which for ease of reading I set off in a with clause).
with
prep as (
select eeid, work_dt, paycode,
row_number() over (partition by eeid order by work_dt)
- row_number() over (partition by eeid, paycode order by work_dt)
as grp
from time_labor
)
select eeid, min(work_dt) as start_eff_dt, max(work_dt) as end_eff_dt
from prep
group by eeid, paycode, grp
having paycode = 'UNPAID'
order by eeid, start_eff_dt
;
The sample data (for testing) and the output from this query can be found in the other answer, which uses match_recognize (Oracle versions >= 12.1 only)