How to count all records with overlapping datetime values

[keb1965]

I have a situation where I need to determine whether or not a record exists where a start datetime and end datetime overlap the start datetime and end datetime of the record.

I have tblEmployees, tblShifts, and tblShiftAssignments. tblShiftAssignments is nothing more than a shift ID and an employee ID. tblShifts has all of the shift data and tblEmployees has all of the employee data.

I need to get a list of all employees that are not assigned to a shift during a specified time frame. For example, lets say we have a shift for 07/19/2013 08:00:00 to 07/19/2013 17:00:00. What I need to do is find every employee that isn't working at all during that time frame. If their shift starts at 16:00:00 then they need to be excluded. If it ends at 08:30:00 they need to be excluded. So to that end, I have come up with this:

@ShiftBegin datetime,
@ShiftEnd datetime

AS

SELECT e.* FROM tblEmployees e
WHERE (SELECT COUNT(*) FROM tblShifts sh
           INNER JOIN tblShiftAssignments sa
              ON sa.SID = sh.ID
           WHERE @ShiftBegin >= sh.ShiftEnd OR @ShiftEnd <= sh.ShiftBegin  -- <---------this is what I am having trouble reconciling in my mind
           AND sa.EID = e.ID) = 0

RETURN

I've loooked this over time and again and something doesn't quite seem right.

The questions I have are:

Will this return an employee record if no records exists in tblShiftAssignments?
Will this work as I expect and filter out all employees who are already scheduled to work at any time between @ShiftBegin and @ShiftEnd?


At the moment, I don't have the ability to test it.

 

[IanRyder]

Hi,

The way to do this is to use two queries, which you are already demonstrating, but the principle to follow is to get a Valid list of Employees which DO FIT the selection criteria and then compare that list against the Employees table again to identify those Employees which DO NOT fall within the Valid selection criteria using IS NULL.

To demonstrate, here is a query to identify all Customers which do not have an Order made in the year 2012. First of all a Sub Query / Derived Table is used to identify all the customers which DO have an order in 2012 and then a List of DISTINCT CustomersID's is returned. This query is then linked to the Customers again using a LEFT OUTER JOIN on the CustomerID to identify those customers, using IS NULL, which do not have an order in 2012.

SELECT     dbo.Customers.CustomerID, dbo.Customers.CompanyName, dbo.Customers.ContactName
FROM         dbo.Customers LEFT OUTER JOIN
                          (SELECT DISTINCT CustomerID
                            FROM          dbo.Orders
                            WHERE      (DATEPART(yyyy, OrderDate) = 2012)) AS CustomersWithOrdersIn2012 ON
                    dbo.Customers.CustomerID = CustomersWithOrdersIn2012.CustomerID
WHERE     (CustomersWithOrdersIn2012.CustomerID IS NULL)

Even though I have embedded a sub query here, you may find it simpler to create a separate saved view to get the Employees which do fit the selection criteria and then link that saved query in the same way as demonstrated above.

All you need to do now is change this to accommodate your situation.

Hope that helps.

Cheers,

Ian

 

[keb1965]

Something like this?

--Get all employees that don't have a shift assignment during the time frame
SELECT e.* FROM tblEmployees e
LEFT OUTER JOIN
--all of the employees who are already assigned a shift during the time frame
(SELECT DISTINCT sa.EID FROM tblShifts sh
	INNER JOIN tblShiftAssignments sa --tblShiftAssignments contains only employeeID(EID) and shiftID(SID)
		ON sa.SID = sh.ID
	WHERE @ShiftBegin BETWEEN sh.ShiftBegin AND sh.ShiftEnd
	   OR @ShiftEnd   BETWEEN sh.ShiftBegin AND sh.ShiftEnd) AS ea --Employee with shift assignments
	ON ea.EID = e.ID --join on the employee ID in the assigned shifts and the employee ID in the employee table
WHERE (ea.EID IS NULL) --Filter out records where there is an employee assigned

Is there room for improvement?

 

[keb1965]

There is a flaw in the logic and I am not sure how to resolve it without manipulating the datetime values and having alot of AND/OR clauses

07-22-2013 08:00:00 is between 07-22-2013 08:00:00 and 07-22-2013 17:00:00, but 07-22-2013 08:00:00 may be the end of the shift ... I need a true between ... also, if there is a shift that starts earlier and ends later than the shift being tested against, I get erronious results.

 

[jmcilhinney]

Unless I'm missing something, your code for detecting overlap should be something like this:

SELECT *
FROM SomeTable
WHERE StartTime < @EndTime
AND EndTime > @StartTime

That will find records that start before your end time and end after your start time. If both those conditions are met then the record has at least partial and possibly complete overlap with your specified period.

 

[keb1965]

Because of the nature of the data, I can't be assured StartTime is always less than EndTime and while I am pretty sure @StartTime will always be before @EndTime, I would feel more comfortable verifying that they are indeed. So, to that end I created a function to test the datetime values and it seems to give the results I am after:

CREATE FUNCTION [dbo].[udfIsOverlapDate]
    (
     @StartO datetime,
     @EndO datetime,
     @StartT datetime,
     @EndT datetime
    )

RETURNS bit

AS

    BEGIN

    DECLARE @MinO datetime
    DECLARE @MaxO datetime
    DECLARE @MinT datetime
    DECLARE @MaxT datetime
    DECLARE @Overlap bit

    IF @EndO < StartO
        BEGIN
             SET @MinO = @EndO
             SET @MaxO = @StartO
        END
    ELSE
        BEGIN
            SET @MinO = @StartO
            SET @MaxO = @EndO
        END

    IF @EndT < StartT
        BEGIN
            SET @MinT = @EndT
            SET @MaxT = @StartT
        END
    ELSE
        BEGIN
            SET @MinT = @StartT
            SET @MaxT = @EndT
        END

    IF @MaxT <= @MinO OR @MinT >= @MaxT
        BEGIN
            SET @Overlap = 0
        END
    ELSE
        BEGIN
            SET @Overlap = 1
        END

    RETURN @Overlap

    END

Now in the select I use this:

SELECT e.* FROM tblEmployees e
    WHERE (SELECT COUNT(*) FROM tblShiftAssignments sa
                   INNER JOIN tblShifts sh
                        ON sa.SID = sh.ID
               WHERE  dbo.udfIsOverlapDate(sh.ShiftBegin, sh.ShiftEnd, @StartTime, @EndTime) = 1
               AND sa.EID = e.ID) = 0
    AND e.Facility = @Facility
    AND e.ActiveEmployee = CASE WHEN @ActiveOnly = 1 THEN 1 ELSE e.ActiveEmployee END
RETURN

I reworked the code to create a distinct set of employees from the first table, and then select where the join is null, but for some reason, this seems to make more sense to me.

Perhaps that is inexperience ...

Which way would be more efficient?