Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT 1 Type, start Date from table
- UNION ALL
- SELECT -1, end from table
- 1 2013-03-01
- 1 2013-05-01
- 1 2013-06-01
- -1 2013-07-10
- -1 2013-07-31
- -1 2013-08-30
- 1 2013-09-15
- 1 2013-10-01
- -1 2013-11-30
- -1 2013-12-31
- SELECT a.Type, a.Date
- , (SELECT SUM(b.Type)
- FROM set1 b
- WHERE b.Date < a.Date
- OR (b.Date = a.Date AND b.Type <= a.Type)) RunningTotal
- FROM set1 a
- 1 2013-03-01 1
- 1 2013-05-01 2
- 1 2013-06-01 3
- -1 2013-07-10 2
- -1 2013-07-31 1
- -1 2013-08-30 0
- 1 2013-09-15 1
- 1 2013-10-01 2
- -1 2013-11-30 1
- -1 2013-12-31 0
- SELECT a.Date Start
- , (SELECT MIN(b.Date)
- FROM set2 b
- WHERE Type = -1
- AND RunningTotal = 0
- AND b.Date > a.Date) End
- FROM set2 a
- WHERE Type = 1 AND RunningTotal = 1
Add Comment
Please, Sign In to add comment