Get dates missing from multiple date ranges

Question

I have one table that stores when a customer support employee is in a particular location and for what date. Each separate date is its own record.

I have a second table that stores a range of dates that customers have asked for onsite support.

I need to extract a list of dates that a given location does NOT have any support representation. All I need is the location and the date(s). I don't care which employee in that location or which customer has requested the support.

So in the sample data below, I need to see as my query results:

+--------+------------+
| London | 04/01/2019 |
| London | 07/01/2019 |
| Paris  | 05/01/2019 |
+--------+------------+

Table: Employee_Location

+----------+----------+------------+
| Employee | Location |    Date    |
+----------+----------+------------+
|     1111 | London   | 01/01/2019 |
|     1111 | London   | 02/01/2019 |
|     1111 | London   | 03/01/2019 |
|     2222 | Paris    | 01/01/2019 |
|     2222 | Paris    | 02/01/2019 |
|     2222 | Paris    | 03/01/2019 |
|     2222 | Paris    | 04/01/2019 |
|     3333 | London   | 05/01/2019 |
|     3333 | Paris    | 06/01/2019 |
|     3333 | Paris    | 07/01/2019 |
|     4444 | London   | 06/01/2019 |
+----------+----------+------------+

Table: Customer_Request

+----------+----------+---------------+------------+
| Customer | Location | Request From  | Request To |
+----------+----------+---------------+------------+
| AAAA     | London   | 01/01/2019    | 06/01/2019 |
| BBBB     | Paris    | 01/01/2019    | 06/01/2019 |
| CCCC     | London   | 05/01/2019    | 07/01/2019 |
+----------+----------+---------------+------------+

Here is my current code ...

select c.CALENDARDTM 
from CALENDAR c, Employee_Location el 
join Customer_Request cron el.location = cr.location 
where c.CALENDARDTM NOT BETWEEN cr.RequestFrom and cr.RequestTo 
   and c.CALENDARDTM between '2019-01-01' AND '2019-01-07'

This is exactly the issue I'm having. How do I express a date that doesn't actually sit in either table? — Goolsy, Dec 21 '18 at 00:28
Member for 5 years but a very rare visitor. Just doing the best I can and hoping to get some direction from people who know much more than I do. — Goolsy, Dec 21 '18 at 03:50
Please start using ANSI style joins. Non-ANSI joins have been deprecated for decades. — Mitch Wheat, Dec 21 '18 at 03:53

score 0 · Answer 1 · answered Dec 21 '18 at 03:44

The key to solving this problem is to create a recordset that contains all dates between your nominated start and end dates.

There are a variety of methods you can use to do this, in the below example I have used a recursive CTE, for larger datasets you will need to tweak this slightly.

Once you have a list of all the dates, you combine it with a list of all locations, so you have all dates at all locations.?

Then you remove all records which match the records that already exists, in the example below a 'Not Exists' is used, but you can use a variety of approaches to get the desired outcome.

CREATE TABLE #Employee_Location (Employee int, [Location] varchar(100), [date] date)

INSERT INTO #Employee_Location (Employee, [Location], [Date])
    VALUES   (1111,'London','2019-01-01')
            ,(1111,'London','2019-01-02')
            ,(1111,'London','2019-01-03')
            ,(2222,'Paris','2019-01-01')
            ,(2222,'Paris','2019-01-02')
            ,(2222,'Paris','2019-01-03')
            ,(2222 ,'Paris','2019-01-04')
            ,(3333,'London','2019-01-05')
            ,(3333,'Paris','2019-01-06')
            ,(3333,'Paris','2019-01-07')
            ,(4444,'London','2019-01-06')



DECLARE @StartDate  date    = '2019-01-01'
DECLARE @EndDate    date    = '2019-01-07'

;WITH Dates AS (
    SELECT @StartDate as d
    UNION ALL
    SELECT DateAdd(d, 1, d) as d
    FROM    Dates
    WHERE   d < @EndDate
)
,Locations AS (
    SELECT DISTINCT [Location]
    FROM #Employee_Location
)
,AllRecords AS (
    SELECT  d
            ,[Location]
    FROM    Dates
                FULL OUTER JOIN Locations
                    ON      1=1
)
SELECT  *
FROM    AllRecords
WHERE   NOT EXISTS (SELECT 1 
                    FROM    #Employee_Location  e
                    WHERE   e.[date] = Allrecords.d
                        AND e.[Location] = Allrecords.[Location])

Get dates missing from multiple date ranges

1 Answers1