How to avoid double counting


/ Published in: SQL
Save to your folder(s)

Use this logic to avoid double counting in ORACLE SQL


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.