CODEWITHSUNDEEP
sql-server
bob_tiamsic
bob_tiamsic

Reputation: 33

Check if time range is between another time range

Im stuck in solving something. How can I check if a time range is between another time range?

For example how can I check if 11:00AM - 12:00PM is between 9:30AM - 2PM? I need this in developing timekeeping system to check if the filed Sick Leave of an employee covers his scheduled break time.

Im using SQL Server 2008

declare @timefrom datetime
declare @timeto datetime
declare @timefromBreak datetime
declare @timetoBreak datetime

set @timefrom = cast('01/21/2015 11:30:00' as datetime)
set @timeto = cast('01/21/2015 14:00:00' as datetime)
set @timefromBreak = cast('01/21/2015 11:00:00' as datetime)
set @timetoBreak = cast('01/21/2015 12:00:00' as datetime)

select 
case when ((@timefromBreak between @timefrom and @timeto) AND (@timetoBreak between @timefrom and @timeto)) then
(datediff(hour, @timefrom,@timeto) - datediff(hour,@timefromBreak,@timetoBreak))

else datediff(hour, @timefrom,@timeto) 

end as TotalLeaveHours

here is my example.. im trying to get the total leave hours an employee filed for his Leave.

The logic is that i will get the total leave hours not including the break time (if and only if the leave time range covers the break time.)

So if an employee filed a leave for 11:30PM to 2:00PM and his scheduled break time is 11:00Am to 12:00PM. The total leave hours should only be 2Hours.

11:30AM to 12:00PM should not be counted for the total leave hours since his scheduled time is 11:00AM to 12:00PM..

Upvotes: 0

Views: 1471

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

This computes the time difference (in minutes at the moment1) and tries to take care of all possible overlaps:

declare @timefrom datetime
declare @timeto datetime
declare @timefromBreak datetime
declare @timetoBreak datetime

select @timefrom = '2015-01-21T11:30:00',@timeto = '2015-01-21T14:00:00',
       @timefromBreak = '2015-01-21T11:00:00',@timetoBreak = '2015-01-21T12:00:00'

select DATEDIFF(minute,@timefrom,@timeto) -
CASE WHEN @timeFrom < @timeToBreak AND @timefromBreak < @timeTo THEN
    DATEDIFF(minute,
        CASE WHEN @timeFrom > @timeFromBreak THEN @timeFrom ELSE @timeFromBreak END,
        CASE WHEN @timeTo < @timeToBreak THEN @timeTo ELSE @timeToBreak END
    ) ELSE 0 END

Hopefully, you can see the basic logic - we always compute the time difference - and then we check whether an overlap exists. Only if an overlap exists do we need to adjust the total time, and we do that using a couple of CASE expressions to account for when the break time and the leave time partially overlap or entirely overlap.

(If SQL Server had MIN and MAX functions that worked against multiple arguments instead of multiple rows, then the later CASE expressions would have just been MAX(@timeFrom,@timeFromBreak) and MIN(@timeTo,@timeToBreak))

1Since in some of your examples you seem to be working with e.g. 2.5 hours, I though it would be safer to work in minutes and let you perform any final adjustment/rounding down/up as a final step, separate from these calculations

Upvotes: 1

bob_tiamsic
bob_tiamsic

Reputation: 33

4 is the total leave hours..

Sorry for making confusions..I need to get the total number of hours..I tried it and i think there will be no conflict if the leave time range will cover the entire break time..how ever if only part of the break time will be covered, the first condition will be false..

if the employee's break schedule is 11:00AM to 12:00PM, then the leave he filed is from 11:30AM to 2:00Pm the Total Leave Hours should return only 3 hours, instead of 2.5hours.

in my testing, 

    declare @timefrom datetime
declare @timeto datetime
declare @timefromBreak datetime
declare @timetoBreak datetime

set @timefrom = cast('01/21/2015 11:30:00' as datetime)
set @timeto = cast('01/21/2015 14:00:00' as datetime)
set @timefromBreak = cast('01/21/2015 11:00:00' as datetime)
set @timetoBreak = cast('01/21/2015 12:00:00' as datetime)

select 
case when ((@timefromBreak between @timefrom and @timeto) AND (@timetoBreak between @timefrom and @timeto)) then
(datediff(hour, @timefrom,@timeto) - datediff(hour,@timefromBreak,@timetoBreak))

else datediff(hour, @timefrom,@timeto) 

end as TotalLeaveHours

this returns 3 hours because it doesn't meet the first condition in Select Case statement.

Upvotes: 0

Related Questions