CODEWITHSUNDEEP
sqldatabaseoracle-databaseoracle12c
chinshuu
chinshuu

Reputation: 61

How to sum up the time excluding the overlapping time with given start datetime and end datetime in Oracle

The data is as below

+----+-------------------+-------------------+
|KEY |START_DTT          |END_DTT            |
+----+-------------------+-------------------+
|KEY1|2020-11-24 00:00:00|2020-11-24 00:10:00|
|KEY1|2020-11-24 00:00:00|2020-11-24 00:20:00|
|KEY1|2020-11-24 00:10:00|2020-11-24 00:30:00|
|KEY1|2020-11-24 00:20:00|2020-11-24 00:50:00|
|KEY1|2020-11-24 00:55:00|2020-11-24 01:00:00|
+----+-------------------+-------------------+

What I want to get is the total sum in minutes excluding the overlapped time in Oracle. Are there any functions or ways to do this?

Upvotes: 0

Views: 203

Answers (1)

Popeye
Popeye

Reputation: 35920

You can use the analytical function and GROUP BY as follows:

SELECT KEY, SUM(DIFF) AS DIFF FROM
(SELECT KEY, 
       MIN(START_DTT) AS START_DTT, 
       MAX(END_DTT) AS END_DTT, 
       ROUND(24*60*(MAX(END_DTT) - MIN(START_DTT)),2) AS DIFF
FROM
(SELECT T.*,
       SUM(CASE WHEN LAG_END_DTT BETWEEN START_DTT AND END_DTT THEN 0 ELSE 1 END) 
        OVER (PARTITION BY KEY ORDER BY START_DTT) AS SM
FROM (SELECT T.*, 
             LAG(END_DTT) OVER (PARTITION BY KEY ORDER BY START_DTT) LAG_END_DTT
        FROM T) T)
GROUP BY KEY, SM)
GROUP BY KEY;

Thanks to VBoka, db<>fiddle

Upvotes: 4

Related Questions