SQL fill days for dates not found in Database

Question

I'm getting data from my function as follows:

Date       |   Number
06-02-2012 |    2
06-05-2012 |    5
06-08-2012 |    5

If i want to include all dates that are not found in DB in the following matter how would i do it?:

Date       |   Number
06-02-2012 |    2
06-03-2012 |    0
06-04-2012 |    0
06-05-2012 |    5
06-06-2012 |    0
06-07-2012 |    0
06-08-2012 |    5

SELECT convert(varchar, MIN(DATEADD(wk, DATEDIFF(wk, 0, person.date), 0)), 1), Count(person.ID) 
FROM [dbo].[Person] person
WHERE (DATEDIFF(D, person.date, @dateFrom) <=0 AND DATEDIFF(D, person.date, @dateTo) >=0) 
GROUP BY DATEPART(WK, person.date)

score 2 · Answer 1 · answered Sep 10 '12 at 08:48

2

You would create a temporary table, or subquery, containing all the dates in your chosen range, and use a left join against your source data

answered Sep 10 '12 at 08:48

podiluska

50,950
7
98
104

You can make that table permanent, too, if you use it quite often. – Salman A Sep 10 '12 at 08:51

score 0 · Accepted Answer · answered Sep 10 '12 at 08:54

I recommend that you create a table of dates -- a one column table containing dates from, say 2000-01-01 to 2050-12-31. You can then use that table on the left hand side of a LEFT JOIN query like this:

SELECT date_table.date AS [Date], COUNT(your_table.primary_key) AS [Number]
FROM date_table
LEFT JOIN your_table ON date_table.date = your_table.date
WHERE date_table.date BETWEEN '2012-01-01' AND '2012-06-30'

Index the date table wisely and you'll end up with a very efficient query.

score 0 · Answer 3 · answered Sep 10 '12 at 09:02

If you need just a small interval, you can try a function like this:

DECLARE @minDate date
DECLARE @maxDate date

SET @minDate = '2012-09-01'
SELECT @maxDate = CAST( CONVERT( CHAR(8), GetDate(), 112) AS DATETIME)


DECLARE @Numbers TABLE(
  Date date,
  Number int)

WHILE @minDate < @maxDate
BEGIN
INSERT INTO @Numbers
SELECT @minDate, 0
WHERE NOT EXISTS( SELECT Number FROM Numbers WHERE [Date] = @minDate )

SET @minDate = DATEADD(day, 1, @minDate)

END

SELECT n.[Date], ISNULL(n.Number, 0) 
FROM @Numbers n
UNION ALL
SELECT Numbers.[Date], ISNULL(Numbers.Number, 0) 
FROM Numbers 
ORDER BY [Date]

If you need more month and year, then I think the best way to make a prefilled permanent helper table with the dates what you need. And make only an easy join on them like it is posted in an other answer.

SQL fill days for dates not found in Database

3 Answers3