creating a list of dates w/o a table

[Nuovo2004]

Hi,
I have a challenge here that I'm sure there is a way around:
I'd like to create a trend query that has running totals per day (including days w/ 0 total). The only problem is that the table I am querying only has records for the days that events took place. e.g.

date total
10/08/2004 20
10/10/2004 15
10/14/2004 3

I would like to fill in the dates that were not there but I am strugging with finding a way to retrieve a set of days into a resultset.
I'd like my output to look like this

date total
10/07/2004 0
10/08/2004 20
10/09/2004 0
10/10/2004 15
etc etc

I've tried doing this with a WHILE statement & incrementing GETDATE()-@N but this results in multiple single record queries. Is there a systable that stores dates or a way to manipulate getdate to return something like the last N days in a column?
Once I have a way to retrieve the dates in this way, I should be able to create a right outer join on my table and replace the null totals with zeros using CASE.
I'd appreciate any help with this.

THANKS!

 

[SQLSister]

Personally I would create a a date table and populate it myself. And join to that.

Questions about posting. See faq183-874

 

[donutman]

You can create the sequence in a temp table and then do your outer join. Here's how to create a table with dates from 8/1/2004 to 10/31/2004

CREATE TABLE #Tmp 
   (ID  int identity (38199,1),
   DateNum  datetime)
   INSERT INTO #Tmp
      SELECT '2004-08-01'  
--  You have to seed the 1st Scope_Identity() value
--  or the while condition might fail
   WHILE Scope_Identity()<DateDiff(dd,0,'2004-11-01') BEGIN
      INSERT INTO #Tmp
         SELECT DateAdd(d,Scope_Identity(),0)
   END
   SELECT * FROM #Tmp
   DROP TABLE #Tmp

BTW, SQLSister, congrats on TipMaster of the week!
-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]

 

[SQLSister]

Thank You Karl.

Questions about posting. See faq183-874

 

[donutman]

Before someone points out the verbose method I used

[Blue]DECLARE[/Blue] @Date [Blue]datetime[/Blue]
[Blue]DECLARE[/Blue] @EndDate [Blue]datetime[/Blue]
[Blue]SET[/Blue] @Date[Gray]=[/Gray][red]'2004-08-01'[/red]
[Blue]SET[/Blue] @EndDate[Gray]=[/Gray][red]'2004-10-31'[/red]
[Blue]CREATE[/Blue] [Blue]TABLE[/Blue] #Tmp
   [Gray]([/Gray]DateFld [Blue]datetime[/Blue][Gray])[/Gray]
[Blue]WHILE[/Blue] @Date[Gray]<[/Gray][Gray]=[/Gray]@EndDate [Blue]BEGIN[/Blue]
   [Blue]INSERT[/Blue] [Blue]INTO[/Blue] #Tmp 
      [Blue]SELECT[/Blue] @Date
   [Blue]SET[/Blue] @Date[Gray]=[/Gray]@Date[Gray]+[/Gray]1
[Blue]END[/Blue]
[Blue]SELECT[/Blue] [Gray]*[/Gray] [Blue]FROM[/Blue] #Tmp
[Blue]DROP[/Blue] [Blue]TABLE[/Blue] #Tmp

-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]

 

[vongrunt]

Nah, we all know what is 38199 for

Personally I'd also use the same temp table to populate results.

 

[r937]

i would use an integers table and use DATEADD with a starting date

an integers table is extremely useful, you will find yourself using over and over

(e.g. search these forums for "integers table" for examples)

rudy

http://r937.com/

SQL Consulting

 

[Nuovo2004]

Thanks Donutman. I was going to post the following:

DROP TABLE #Tmp
declare @setdate as datetime
set @setdate  = getdate()-30

CREATE TABLE #Tmp 
   (DateNum  datetime)

   WHILE @setdate < convert(varchar(10),getdate(),101)
   BEGIN
      INSERT INTO #Tmp
         SELECT @setdate
         SET @setdate = @setdate + 1
   END

I now have the join I need. I also like your idea SQL sister but I want to avoid permanently adding to the schema since I'm just setting up a reporting solution.

 

[donutman]

convert(varchar(10),getdate(),101)
could be just
GetDate()
-Karl

 

[Nuovo2004]

Hey Donutman,
getdate() returns a timestamp. I don't want a timestamp. Does your getdate() return exactly the same format as 101?

 

[donutman]

That shouldn't effect the WHILE, but to start the @SetDate variable you can use:

   @SetDate=DateAdd(dd,DateDiff(dd,0,GetDate()),0)

It's a nice trick, you can use it to get the 1st day of month by replacing the dd with m...and many other substitutions work.
-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]