Oracle query - select date and time - Overlapping

Question

oralce query Date and time with overlapping

ID    startdate                enddate                      hours
a124     10/10/2019 07:30:00    10/10/2019 11:30:00            4
a124     10/10/2019 07:00:00    10/10/2019 15:10:00            8.17
bc24     10/10/2019 07:30:00    10/10/2019 11:30:00            4
bc24     10/10/2019 10:30:00    10/10/2019 15:30:00            5
er67     10/10/2019 09:30:00    10/10/2019 11:30:00            2
er67     10/10/2019 15:30:00    10/10/2019 16:30:00            1

Desired Output :

    ID    startdate                enddate                      hours
   a124   10/10/2019 07:00:00     10/10/2019 15:10:00           8.17
   bc24   10/10/2019 07:30:00     10/10/2019 15:30:00            8
   er67   10/10/2019 09:30:00     10/10/2019 11:30:00            2
   er67   10/10/2019 15:30:00     10/10/2019 16:30:00            1

Answer 1

I would approach this using lag(), a cumulative sum() and aggregation. Here is a step by step explanation.

First, you can use lag() to recover the previous start and end date for the same id:

select 
    t.*,
    lag(startdate) over(partition by id order by startdate) lagstartdate,
    lag(enddate)   over(partition by id order by startdate) lagenddate
from mytable t

ID   | STARTDATE           | ENDDATE             | HOURS | LAGSTARTDATE        | LAGENDDATE         
:--- | :------------------ | :------------------ | ----: | :------------------ | :------------------
a124 | 2019-10-10 07:00:00 | 2019-10-10 15:10:00 |  8.17 | null                | null               
a124 | 2019-10-10 07:30:00 | 2019-10-10 11:30:00 |     4 | 2019-10-10 07:00:00 | 2019-10-10 15:10:00
bc24 | 2019-10-10 07:30:00 | 2019-10-10 11:30:00 |     4 | null                | null               
bc24 | 2019-10-10 10:30:00 | 2019-10-10 15:30:00 |     5 | 2019-10-10 07:30:00 | 2019-10-10 11:30:00
er67 | 2019-10-10 09:30:00 | 2019-10-10 11:30:00 |     2 | null                | null               
er67 | 2019-10-10 15:30:00 | 2019-10-10 16:30:00 |     1 | 2019-10-10 09:30:00 | 2019-10-10 11:30:00

---

Then, you can set up the cumulative sum to slit records having the same id within groups (that will later on be aggregated). When the dates do not overlap, then a new group starts:

select 
    t.*,
    sum(
        case when startdate <= lagenddate or enddate <= lagstartdate
            then 0
            else 1 
        end
    ) over(partition by id order by startdate) grp
from (
    select 
        t.*,
        lag(startdate) over(partition by id order by startdate) lagstartdate,
        lag(enddate)   over(partition by id order by startdate) lagenddate
    from mytable t
) t

ID   | STARTDATE           | ENDDATE             | HOURS | LAGSTARTDATE        | LAGENDDATE          | GRP
:--- | :------------------ | :------------------ | ----: | :------------------ | :------------------ | --:
a124 | 2019-10-10 07:00:00 | 2019-10-10 15:10:00 |  8.17 | null                | null                |   1
a124 | 2019-10-10 07:30:00 | 2019-10-10 11:30:00 |     4 | 2019-10-10 07:00:00 | 2019-10-10 15:10:00 |   1
bc24 | 2019-10-10 07:30:00 | 2019-10-10 11:30:00 |     4 | null                | null                |   1
bc24 | 2019-10-10 10:30:00 | 2019-10-10 15:30:00 |     5 | 2019-10-10 07:30:00 | 2019-10-10 11:30:00 |   1
er67 | 2019-10-10 09:30:00 | 2019-10-10 11:30:00 |     2 | null                | null                |   1
er67 | 2019-10-10 15:30:00 | 2019-10-10 16:30:00 |     1 | 2019-10-10 09:30:00 | 2019-10-10 11:30:00 |   2

---

Finally, you can group the records by id and grp: min() and max() give you the date range, then you can compute the date difference.

Final query:

select 
    id, 
    min(startdate) startdate,
    max(enddate) enddate,
    round((max(enddate) - min(startdate)) * 24, 2) hours
from (
    select 
        t.*,
        sum(
            case when startdate <= lagenddate or enddate <= lagstartdate
                then 0
                else 1 
            end
        ) over(partition by id order by startdate) grp
    from (
        select 
            t.*,
            lag(startdate) over(partition by id order by startdate) lagstartdate,
            lag(enddate)   over(partition by id order by startdate) lagenddate
        from mytable t
    ) t
) t
group by id, grp
order by id, grp

ID   | STARTDATE           | ENDDATE             | HOURS
:--- | :------------------ | :------------------ | ----:
a124 | 2019-10-10 07:00:00 | 2019-10-10 15:10:00 |  8.17
bc24 | 2019-10-10 07:30:00 | 2019-10-10 15:30:00 |     8
er67 | 2019-10-10 09:30:00 | 2019-10-10 11:30:00 |     2
er67 | 2019-10-10 15:30:00 | 2019-10-10 16:30:00 |     1

Demo on DB Fiddle

Answer 2

You can use analytical functions (LAG and SUM) as following to make groups of dates without gaps:

SQL> SELECT
  2      ID,
  3      MIN(STARTDATE),
  4      MAX(ENDDATE),
  5      ROUND(SUM(CASE
  6          WHEN PREV_ENDDATE BETWEEN STARTDATE AND ENDDATE THEN ENDDATE - PREV_ENDDATE
  7          ELSE ENDDATE - STARTDATE
  8      END) * 24, 2) AS HOURS
  9  FROM
 10      (
 11          SELECT
 12              T.*,
 13              SUM(CASE
 14                  WHEN T.PREV_ENDDATE < T.STARTDATE THEN 1
 15              END) OVER(
 16                  PARTITION BY ID
 17                  ORDER BY
 18                      STARTDATE, ENDDATE
 19              ) SM
 20          FROM
 21              (
 22                  SELECT
 23                      ID,
 24                      STARTDATE,
 25                      ENDDATE,
 26                      LAG(ENDDATE) OVER(
 27                          PARTITION BY ID
 28                          ORDER BY
 29                              STARTDATE, ENDDATE
 30                      ) AS PREV_ENDDATE
 31                  FROM
 32                      T TOUT
 33                  WHERE
 34                      NOT EXISTS (
 35                          SELECT
 36                              1
 37                          FROM
 38                              T TIN
 39                          WHERE
 40                              TIN.ID = TOUT.ID
 41                              AND TOUT.STARTDATE BETWEEN TIN.STARTDATE AND TIN.ENDDATE
 42                              AND TOUT.ENDDATE BETWEEN TIN.STARTDATE AND TIN.ENDDATE
 43                              AND TOUT.ROWID <> TIN.ROWID
 44                      )
 45              ) T
 46      )
 47  GROUP BY
 48      ID,
 49      SM;

ID   MIN(START MAX(ENDDA      HOURS
---- --------- --------- ----------
a124 10-OCT-19 10-OCT-19       8.17
bc24 10-OCT-19 10-OCT-19          8
er67 10-OCT-19 10-OCT-19          1
er67 10-OCT-19 10-OCT-19          2

SQL>

Cheers!!