Advanced Search

Return to Snippet

Revision: 27386
at June 4, 2010 15:01 by mprabhuram
Initial Code 
SQL> select * from t;

START END_D         ID
----- ----- ----------
10:00 10:45          1
10:30 11:45          1
12:30 12:45          1
12:40 13:01          1
10:10 10:20          1
12:35 13:20          1
10:00 10:45          2
10:55 11:00          2
10:00 10:30          2

9 rows selected.

SQL> select
  2  --    case when start_dat between lgs and lge then lge else start_dat end start_dat,
  3  --    end_dat,
  4      id,
  5      sum(24 * 60 * (end_dat - case when start_dat between lgs and lge then lge else start_dat end)) diff
  6    from (
  7      select id, start_dat,
  8          lag(start_dat) over (partition by id order by start_dat) lgs,
  9          lag(end_dat) over (partition by id order by start_dat) lge,
 10          end_dat
 11        from (
 12          select id, start_dat,
 13              lag(start_dat) over (partition by id order by start_dat) lgsi,
 14              lag(end_dat) over (partition by id order by start_dat) lgei,
 15              end_dat
 16            from t
 17        )
 18        where end_dat > lgei or lgsi is null
 19    )
 20    group by id
 21  /

        ID       DIFF
---------- ----------
         1        155
         2         50
Initial URL 
http://www.experts-exchange.com/Database/Oracle/Q_22861266.html
Initial Description 
Use this logic to avoid double counting in ORACLE SQL
Initial Title 
How to avoid double counting
Initial Tags 
sql, Oracle
Initial Language 
SQL