CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2008-r2
good-to-know
good-to-know

Reputation: 742

How to detect overlapping between two datetime in SQL?

I have a table like below:

EmpId   DateTimeIn                  DateTimeOut                 Effort
1030    2016-12-01 07:30:00.000     2016-12-01 12:30:00.000     1030
1030    2016-12-01 13:30:00.000     2016-12-01 16:30:00.000     1531

The employee 1030 has inserted his effort on 2016-12-01 for the time ranges 07:30 to 12:30 and 13:30 to 16:30.

After this he should not enter his effort on these time ranges. He can enter effort prior 07:30 or 12:30 to 13:30 or beyond 16:30 for 2016-12-01 or can enter for any other day.

To achieve this I write a below query but it is always throwing error and not allowing users to insert data. Please help.

IF EXISTS (SELECT 1 FROM TimesheetEntries 
WHERE EmpId = @EmpId AND (@DateTimeIn >= DateTimeIn AND @DateTimeIn < DateTimeOut)  
OR (@DateTimeOut >= DateTimeOut AND @DateTimeOut < DateTimeIn))
BEGIN
    RAISERROR ('You already input your effort for the given time range.',16,1)
    RETURN 
END

Upvotes: 4

Views: 855

Answers (3)

3N1GM4
3N1GM4

Reputation: 3351

Looks like a bracketing issue - you're not ANDing both parts of your OR with the EmpId = @EmpId. Also looks like the logic is slightly faulty (you check that @DateTimeOut is later than DateTimeOut and earlier than DateTimeIn, which if DateTimeOut > DateTimeIn as we'd expect, will never be satisfied).

Try:

IF EXISTS 
(
    SELECT 1 
    FROM TimesheetEntries 
    WHERE EmpId = @EmpId 
    AND ((@DateTimeIn >= DateTimeIn AND @DateTimeIn <= DateTimeOut)  OR (@DateTimeOut >= DateTimeIn AND @DateTimeOut <= DateTimeOut))
)
BEGIN
    RAISERROR ('You already input your effort for the given time range.',16,1)
    RETURN 
END

Upvotes: 3

Cato
Cato

Reputation: 3701

I found once that these expressions can be simplified - if an overlap is to be detected, the time in has to be before the end of an existing shift you are comparing with - if that is the case, then the worker must also have worked after the start of the existing shift - so it can boil down to two key conditions

IF EXISTS (SELECT 1 FROM TimesheetEntries 
WHERE EmpId = @EmpId AND @DateTimeIn  < DateTimeOut  
AND @DateTimeOut > DateTimeIn)   --has to have started before end AND  ended after the start
BEGIN
RAISERROR ('You already input your effort for the given time range.',16,1)
RETURN 
END

Upvotes: 2

Mohamed Thaufeeq
Mohamed Thaufeeq

Reputation: 1677

This query will inquire in all conditions and work for your case.

NOTE: @3N1GM4's answer is shorter than me and also work for your case.

IF EXISTS 
(
    select TOP 1 1 from HR.TimesheetEntries where SubmittedByEmpId = 1030 AND 
(
(DateTimeIn > @DateTimeIn AND DateTimeIn < @DateTimeIn) OR (DateTimeOut > @DateTimeOut AND DateTimeOut < @DateTimeOut) OR
(DateTimeIn > @DateTimeIn AND DateTimeOut < @DateTimeIn) OR (DateTimeOut > @DateTimeOut AND DateTimeIn < @DateTimeOut) OR
(DateTimeOut > @DateTimeIn AND DateTimeIn < @DateTimeIn) OR (DateTimeIn > @DateTimeOut AND DateTimeOut < @DateTimeOut) OR
(DateTimeOut > @DateTimeIn AND DateTimeOut < @DateTimeIn) OR (DateTimeOut > @DateTimeOut AND DateTimeOut < @DateTimeOut)
)
)
BEGIN
    RAISERROR ('You already input your effort for the given time range.',16,1)
    RETURN 
END

Upvotes: 1

Related Questions