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

Answer 1

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