Advanced Search

How to avoid double counting

/ Published in: SQL
Save to your folder(s)
Use this logic to avoid double counting in ORACLE SQL

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. SQL> SELECT * FROM t;
  2.  
  3. START END_D         ID
  4. ----- ----- ----------
  5. 10:00 10:45          1
  6. 10:30 11:45          1
  7. 12:30 12:45          1
  8. 12:40 13:01          1
  9. 10:10 10:20          1
  10. 12:35 13:20          1
  11. 10:00 10:45          2
  12. 10:55 11:00          2
  13. 10:00 10:30          2
  14.  
  15. 9 ROWS selected.
  16.  
  17. SQL> SELECT
  18.   2  --    case when start_dat between lgs and lge then lge else start_dat end start_dat,
  19.   3  --    end_dat,
  20.   4      id,
  21.   5      SUM(24 * 60 * (end_dat - CASE WHEN start_dat BETWEEN lgs AND lge THEN lge ELSE start_dat END)) diff
  22.   6    FROM (
  23.   7      SELECT id, start_dat,
  24.   8          lag(start_dat) OVER (partition BY id ORDER BY start_dat) lgs,
  25.   9          lag(end_dat) OVER (partition BY id ORDER BY start_dat) lge,
  26.  10          end_dat
  27.  11        FROM (
  28.  12          SELECT id, start_dat,
  29.  13              lag(start_dat) OVER (partition BY id ORDER BY start_dat) lgsi,
  30.  14              lag(end_dat) OVER (partition BY id ORDER BY start_dat) lgei,
  31.  15              end_dat
  32.  16            FROM t
  33.  17        )
  34.  18        WHERE end_dat > lgei OR lgsi IS NULL
  35.  19    )
  36.  20    GROUP BY id
  37.  21  /
  38.  
  39.         ID       DIFF
  40. ---------- ----------
  41.          1        155
  42.          2         50

URL: http://www.experts-exchange.com/Database/Oracle/Q_22861266.html

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.