CODEWITHSUNDEEP
sqlsql-servert-sql
user4615254
user4615254

Reputation:

Select intersecting times from a table

My table is as follows:

ShiftID, StoreName, StartDate, EndDate, StartTime, EndTime, ShiftNo.

I need to select all shifts that intersect e.g Say there are 2 shifts in a day (ShiftNo. column), if shift number 2 starts before shift number 1 ends then both shifts should appear in the result set.

Any help is hugely appreciated.

Thanks all.

Upvotes: 1

Views: 46

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

First, having dates and times in separate columns is not useful for this. So, let's combine them. This may require adjusting the data types:

with s as (
      select s.*,
             (cast(startDate as datetime) + starttime) as startdatetime,
             (cast(endDate as datetime) + endtime) as enddatetime
      from shifts s
     )
select s.*
from s
where exists (select 1
              from s s2
              where s.startdatetime < s2.enddatetime and
                    s.enddatetime > s2.startdatetime
             );

Upvotes: 1

Related Questions