Insert missing dates within table I have a table with a column for ID, Date and Frequency of occurrence for that particular date and ID.

ID    date          Frequency
1     2014-05-18      5
1     2014-05-19      4
1     2014-05-20      25
2     2014-05-20      7
3     2014-05-18      4
3     2014-05-20      1
4     2014-05-18      6


I'm looking to insert the dates for each ID that aren't currently there (assuming that the date ranges from 2014-05-18 to 2014-05-20) and add a value of 0 in Frequency for those.

For example, you can see that

ID 1 has an observation for all three dates;

ID 2 is missing 2014-05-18 and 2014-05-19; 

ID 3 is missing 2014-05-19; 

ID 4 is missing 2014-05-19 and 2014-05-20.

Therefore, the output should look like this

ID    date          Frequency
1     2014-05-18      5
1     2014-05-19      4
1     2014-05-20      25
2     2014-05-18      0
2     2014-05-19      0
2     2014-05-20      7
3     2014-05-18      4
3     2014-05-19      0
3     2014-05-20      1
4     2014-05-18      6
4     2014-05-19      0
4     2014-05-20      0


I've attempted the following from this question. However, that will only add dates with 0 Frequency that aren't present throughout the entire table instead of on an ID level.


DECLARE @s DATE = '20140518', @e DATE = '20140520';

SELECT c.date, Frequency = COALESCE(a.Frequency,0)
FROM dbo.Calendar AS c
LEFT OUTER JOIN dbo.table AS a
ON c.date = s.[date]
WHERE c.d &gt;= @s
AND c.d &lt; DATEADD(DAY, 1, @e);
Using your base sample query above, this code should do what you are looking for. The important changes:


CROSS JOIN to a list of IDs that should be in the report
JOIN to the data table using Date and ID
SELECT the id from the CROSS JOIN table


The code:

DECLARE @s DATE = '20140518', @e DATE = '20140520';

SELECT 
    i.ID, 
    c.[date], 
    Frequency = COALESCE(a.Frequency,0)
FROM 
    dbo.Calendar AS c
CROSS JOIN (SELECT DISTINCT x.ID FROM dbo.table x WHERE x.date &gt;= @s AND x.date &lt; DATEADD(day, 1, @e)) AS i
LEFT OUTER JOIN 
    dbo.table AS a
ON 
    c.[date] = a.[date]
    AND a.ID = i.ID
WHERE 
    c.[date]&gt;= @s
    AND c.[date] &lt; DATEADD(DAY, 1, @e);