T-SQL query to find missing IDs for a date range

Question

Given these tables

table Channel
--------------
ChannelID int IDENTITY
<other irrelevant stuff>

table Program
--------------
ProgramID   int IDENTITY
ChannelID   int
AiringDate  datetime
<other irrelevant stuff>

and this query

SELECT   C.ChannelID, t.AiringDate
FROM 
Channel C
LEFT JOIN ( 
    SELECT distinct ChannelID
    FROM   Program 
    WHERE AiringDate = '2010-01-16'
) p 
        ON p.ChannelID=C.ChannelID 
CROSS JOIN (
    SELECT AiringDate = '2010-01-16'
) t
WHERE   C.ChannelID IN (1, 2, 74, 15, 906)  /* the Channel table contains more channels than we are interested in */
    AND p.ChannelID IS NULL

which yields

ChannelID | AiringDate
----------|-----------
    2     | 2010-01-16
   906    | 2010-01-16

how can I modify it to accept a date range, so that the result will be something like

ChannelID | AiringDate
----------|-----------
    2     | 2010-01-16
   906    | 2010-01-16
    2     | 2010-01-17
   906    | 2010-01-17

if there were no programs aired on these 2 channels any of these two days

This returns no rows

SELECT   C.ChannelID, t.AiringDate
FROM 
Channel C
LEFT JOIN ( 
    SELECT distinct ChannelID, AiringDate
    FROM   Program 
    WHERE AiringDate between '2010-01-16' and '2010-01-17'
) p 
        ON p.ChannelID=C.ChannelID 
CROSS JOIN (
    SELECT AiringDate = '2010-01-16'
    union
    SELECT AiringDate = '2010-01-17'
) t
WHERE   C.ChannelID IN (1, 2, 74, 15, 906) 
    AND p.ChannelID IS NULL

That CROSS JOIN is a bit ugly, and it would be nice to get rid of it altogether. Note that the first example query is a bit convoluted; For single dates I have a simpler one that only outputs missing ChannelIDs:

SELECT   C.ChannelID
FROM 
Channel C
LEFT JOIN ( 
    SELECT distinct ChannelID
    FROM   Program 
    WHERE  AiringDate = '2010-01-16'
) p 
        ON p.ChannelID=C.ChannelID 
WHERE   C.ChannelID IN (1, 2, 74, 15, 906) 
    AND p.ChannelID IS NULL

Lieven Keersmaekers · Accepted Answer · 2010-02-23T11:21:09.847

2

If I understand correct, this should get what you requested.

Combine all Channels with every AiringDate
Select all Channels having AiringDates between the dates you need.
LEFT JOIN to remove all Channels already having AiringDates

SQL Statement

DECLARE @Channel TABLE (ChannelID INTEGER PRIMARY KEY)
DECLARE @Program TABLE (ProgramID INTEGER PRIMARY KEY, ChannelID INTEGER, AiringDate DATETIME)

INSERT INTO @Channel VALUES (1) -- Aired on 16 & 17
INSERT INTO @Channel VALUES (2) -- Aired on 16
INSERT INTO @Channel VALUES (3) -- Not Aired

INSERT INTO @Program VALUES (1, 1, '01-16-2010')
INSERT INTO @Program VALUES (2, 1, '01-17-2010')
INSERT INTO @Program VALUES (3, 2, '01-16-2010')


SELECT  C.*
FROM    (
          SELECT  C.ChannelID 
                  , p.AiringDate 
          FROM    @Channel C        
                  CROSS JOIN ( 
                    SELECT  DISTINCT AiringDate 
                    FROM    @Program 
                    WHERE   AiringDate BETWEEN '01-16-2010' AND '01-17-2010'
                  ) p 
        ) c
        LEFT OUTER JOIN (
          SELECT  C.ChannelID, p.AiringDate
          FROM    @Channel C
                  INNER JOIN @Program p ON p.ChannelID = C.ChannelID
          WHERE   AiringDate BETWEEN '01-16-2010' AND '01-17-2010' 
        ) p ON p.ChannelID = C.ChannelID AND p.AiringDate = C.AiringDate
WHERE   p.ChannelID IS NULL AND p.AiringDate IS NULL

edited Feb 23 '10 at 11:21

answered Feb 23 '10 at 09:23

Lieven Keersmaekers

57,207
13
112
146

Hmm, close! Though, it only works if there were no programs on the 16th AND there were no programs on the 17th. My example might've been lacking. I want to find the channels which didn't air any programs within a certain date range, and also return which AiringDates that might've been. So one row per channel and per AiringDate. – J F Feb 23 '10 at 10:35
Actually, apart from the ugly select/union which you got rid of (which is nice!) your query does the exact same thing as the second one listed in my original post. If I remove my WHERE C.ChannelID IN (1, 2, 74, 15, 906) stipulation (which your query omitted), I get the same results. The execution plans are identical. – J F Feb 23 '10 at 10:47
@ J F - I have added some sample data. For this data, what would you expect as output? I was under the impression it should return ChannelID 3 for each date. – Lieven Keersmaekers Feb 23 '10 at 10:50
Ah, nice. It should also return the row [ChannelID: 2, AiringDate:'01-17-2010'], since that didn't air anything on the 17th. I only get two rows with ChannelID: 3 and the respective dates. – J F Feb 23 '10 at 10:58

T-SQL query to find missing IDs for a date range

1 Answers1