CODEWITHSUNDEEP
sqlsql-server
Arash Ghasemi Rad
Arash Ghasemi Rad

Reputation: 314

find time conflicts between two dates

I am working on a query that should find lunch times between two dates. for example, lunch time is between 14:00 PM and 15:30 PM. and dates are: '2016-06-09 10:00:00.000' and '2016-06-11 10:00:00.000' lunch time occures two times between these dates. and another examples:

'2016-06-09 15:00:00.000' and '2016-06-11 10:00:00.000' : 2 Times
'2016-06-09 17:00:00.000' and '2016-06-11 10:00:00.000' : 1 Time
'2016-06-09 13:00:00.000' and '2016-06-11 15:00:00.000' : 3 Times

but I can never do it :(

Upvotes: 2

Views: 330

Answers (2)

Chaos Legion
Chaos Legion

Reputation: 2970

Try this and make tweeks according to your requirements:

DECLARE @date1 DATETIME = '2016-06-09 08:30:00.000';
DECLARE @date2 DATETIME = '2016-06-13 18:00:00.000';
DECLARE @lunchStart DATETIME = '2016-06-13 14:00:00.000';
DECLARE @lunchEnd DATETIME = '2016-06-13 15:30:00.000';
DECLARE @output INT = 0;

SELECT @output = DATEDIFF(DAY, @date1, @date2)
IF DATEPART(HOUR, @date1) > DATEPART(HOUR, @lunchStart) OR (DATEPART(HOUR, @date1) = DATEPART(HOUR, @lunchStart) AND DATEPART(MINUTE, @date1) <= DATEPART(MINUTE, @lunchStart))
    SET @output = @output - 1

IF DATEPART(HOUR, @date2) < DATEPART(HOUR, @lunchEnd) OR (DATEPART(HOUR, @date2) = DATEPART(HOUR, @lunchEnd) AND DATEPART(MINUTE, @date2) = DATEPART(MINUTE, @lunchEnd))
    SET @output = @output - 1

PRINT @output

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

This seems to work:

declare @t table (StartAt datetime not null,EndBefore datetime not null)
insert into @t(StartAt,EndBefore) values
('2016-06-09T15:00:00.000','2016-06-11T10:00:00.000'),
('2016-06-09T17:00:00.000','2016-06-11T10:00:00.000'),
('2016-06-09T13:00:00.000','2016-06-11T15:00:00.000')

;With Dates as (
    select MIN(DATEADD(day,DATEDIFF(day,0,StartAt),0)) as ADate
    from @t
    union all
    select DATEADD(day,1,ADate) from Dates
    where exists (select * from @t where EndBefore > DATEADD(day,1,ADate))
), Lunches as (
    select DATEADD(minute,(14*60),ADate) as StartAt,
           DATEADD(minute,(15*60+30),ADate) as EndBefore
    from Dates
)
select
    *,(select COUNT(*) from Lunches l
       where l.StartAt < t.EndBefore and t.StartAt < l.EndBefore)
from @t t

Where we used two CTEs to (a) derive all relevant Dates, and (b) from the dates, compute all possible lunch times, before finally just finding all lunches that overlap the original periods.

Result:

StartAt                 EndBefore               
----------------------- ----------------------- -----------
2016-06-09 15:00:00.000 2016-06-11 10:00:00.000 2
2016-06-09 17:00:00.000 2016-06-11 10:00:00.000 1
2016-06-09 13:00:00.000 2016-06-11 15:00:00.000 3

Note - many people overcomplicate trying to work out a definition of overlaps. I'm using a simple definition here and usually the only thing that might require alteration is deciding on whether to use < or <=, which depends on whether you consider two periods that abut but otherwise do not cover the same time periods to be overlapped.

So here, you might want to change the definitions if you're unhappy with the answers the above query produces when you're querying for periods that start at exactly 15:30 or end at exactly 14:00.

Upvotes: 4

Related Questions