Find Missing Dates 1

[CasperTFG]

Hello All,

I want to see if there is a simple way to do this. Basically I have a DB that keeps daily entries. Well some days there are no entries because the system was down.

I can easily find how many entries I have on the days that I have entries.

SELECT CONVERT(char(12), setsysdt, 2) AS NewDate, COUNT(*) As StatCount FROM statlog 
GROUP BY CONVERT(char(12), setsysdt, 2)
ORDER BY CONVERT(char(12), setsysdt, 2)

But how do I find or include the days that have 0 entries?


Casper

There is room for all of gods creatures, "Right Beside the Mashed Potatoes".

 

[SQLSister]

The easiest way I can think of is to have a table of dates and then left join to it.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[donutman]

There are several ways to do this. Let's see how feel about one easy method that I can think of. Would it be ok to enter a row into your table for every date and leave some column as blank or null to indicate that nothing happened that day? Obviously, you wouldn't enter this row if something had already happened.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[CasperTFG]

Hi all...

I actually got it done this way...

DECLARE @Date datetime
DECLARE @EndDate datetime
SET @Date='2003-01-23'
SET @EndDate='2004-11-18'
CREATE TABLE T_Dates
   (DateFld datetime)
WHILE @Date<=@EndDate BEGIN
   INSERT INTO T_Dates 
      SELECT @Date
   SET @Date=@Date+1
END

SELECT CONVERT(char(12), D.DateFld, 2) AS StatDate, COUNT(S.setsysdt) AS StatCount FROM T_Dates AS D
LEFT OUTER JOIN statlog AS S ON CONVERT(char(12), D.DateFld, 2) = CONVERT(char(12), setsysdt, 2)
GROUP BY CONVERT(char(12), D.DateFld, 2)
ORDER BY CONVERT(char(12), D.DateFld, 2)

DROP TABLE T_Dates

Casper

There is room for all of gods creatures, "Right Beside the Mashed Potatoes".

 

[donutman]

Good for you Casper, but make that a temporary table and you won't use as much resources. Good Job!
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]