Guest User

Untitled

a guest
Jan 22nd, 2018
69
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.77 KB | None | 0 0
  1. SELECT 1 Type, start Date from table
  2. UNION ALL
  3. SELECT -1, end from table
  4.  
  5. 1 2013-03-01
  6. 1 2013-05-01
  7. 1 2013-06-01
  8. -1 2013-07-10
  9. -1 2013-07-31
  10. -1 2013-08-30
  11. 1 2013-09-15
  12. 1 2013-10-01
  13. -1 2013-11-30
  14. -1 2013-12-31
  15.  
  16. SELECT a.Type, a.Date
  17. , (SELECT SUM(b.Type)
  18. FROM set1 b
  19. WHERE b.Date < a.Date
  20. OR (b.Date = a.Date AND b.Type <= a.Type)) RunningTotal
  21. FROM set1 a
  22.  
  23. 1 2013-03-01 1
  24. 1 2013-05-01 2
  25. 1 2013-06-01 3
  26. -1 2013-07-10 2
  27. -1 2013-07-31 1
  28. -1 2013-08-30 0
  29. 1 2013-09-15 1
  30. 1 2013-10-01 2
  31. -1 2013-11-30 1
  32. -1 2013-12-31 0
  33.  
  34. SELECT a.Date Start
  35. , (SELECT MIN(b.Date)
  36. FROM set2 b
  37. WHERE Type = -1
  38. AND RunningTotal = 0
  39. AND b.Date > a.Date) End
  40. FROM set2 a
  41. WHERE Type = 1 AND RunningTotal = 1
Add Comment
Please, Sign In to add comment