CODEWITHSUNDEEP
sqloverlap
Nguyễn Xuân Hoàng
Nguyễn Xuân Hoàng

Reputation: 63

Select each item without overlap time SQL

Item    |   StartTime           |   EndTime
1       |   2015-08-15 03:00:00 |   2015-08-17 12:00:00
1       |   2015-08-15 07:00:00 |   2015-08-17 18:00:00
1       |   2015-08-18 03:00:00 |   2015-08-20 12:00:00
2       |   2015-08-15 03:00:00 |   2015-08-17 12:00:00
2       |   2015-08-15 07:00:00 |   2015-08-17 18:00:00
2       |   2015-08-19 04:00:00 |   2015-08-20 12:00:00

so, if i select non overlap objects, result should be:

1       |   2015-08-18 03:00:00 |   2015-08-20 12:00:00
2       |   2015-08-19 04:00:00 |   2015-08-20 12:00:00

Upvotes: 0

Views: 51

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

Explain

  • first try to find a match where two ranges overlap T1.StartTime <= T2.EndTime ...
  • but have to be a range for the same item T1.item = T2.item
  • and have to be a different range T1.StartTime <> T2.StartTime and T1.EndTime <> T2.EndTime
  • if can't find a match mean no overlap T2.item IS NULL

SqlFiddle Demo

 SELECT T1.*
 FROM YourTable T1
 LEFT JOIN YourTable T2
   ON (T1.StartTime <= T2.EndTime)  and  (T1.EndTime >= T2.StartTime)
  and T1.item = T2.item
  and T1.StartTime <> T2.StartTime
  and T1.EndTime <> T2.EndTime
 WHERE 
     T2.item IS NULL

OUTPUT

| Item |                StartTime |                  EndTime |
|------|--------------------------|--------------------------|
|    1 | August, 18 2015 03:00:00 | August, 20 2015 12:00:00 |
|    2 | August, 19 2015 04:00:00 | August, 20 2015 12:00:00 |

Upvotes: 2

Related Questions