Reputation: 37
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)
Upvotes: 0
Views: 448
Reputation: 7695
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.
Upvotes: 0
Reputation: 272446
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.
Upvotes: 0
Reputation: 51514
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
Upvotes: 2