overlap booking problem

• sqlnewby

  Right there with Babe

  Points: 797

  More actions

  October 11, 2004 at 8:32 am

  #162388

  Hi

   i am looking for an sql statement which returns the overlapping date ranges

  here is the data in my table

  bookingid    start                 finish

  1001         2004/10/11 08:00      2004/10/11 17:30

  1002         2004/10/11 09:00      2004/10/11 14:30

  1003         2004/10/11 10:00      2004/10/11 18:00

  1004         2004/10/11 11:00      2004/10/11 12:30

  1005         2004/10/11 18:00      2004/10/11 19:30

  i only wanted to return

  1001

  1002

  1003

  1004

  thanks for any help

  mrpro.

• Morten Cyren

  SSC Veteran

  Points: 261

  More actions

  October 11, 2004 at 9:08 am

  #526194

  if i understand you correct.

  SET dateformat dmy

  Declare @starttime datetime

  Declare @stoptime datetime

  Set @starttime = '11.10.2004 08:00:00'

  Set @stoptime = '11.10.2004 17:59:59'

  SELECT * FROM <your table> WHERE start between @starttime and @stoptime AND finish between @starttime and @stoptime

  Morten

• sqlnewby

  Right there with Babe

  Points: 797

  More actions

  October 11, 2004 at 9:17 am

  #526196

  Thank you for replying Morten,

   what i want is only the overlapped bookingid's (date ranges)..in the sample i have drawn only the first 4 should return as they are overlapped with each other..

   

  thanks