[dba-SQLServer] Reports

You have to use the variable table to drive the select, assuming it has
all the dates in it.  Get the dates from there and the matching records
from the primary table.  That should give you all the dates.

Charlotte Foust


-----Original Message-----
From: Francisco Tapia [mailto:fhtapia at gmail.com] 
Sent: Monday, December 13, 2004 2:18 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Reports


HA!, I guess i was pretty vauge ;) 

I'm going to be graphing some data out for the boss (kinda last minute
changes) (wait when is there not :P)

the original select spits out a set of data that looks very similar to
the listing below

8/1/2003, 1, SampleText, Qty1
10/1/2003, 1, SampleText, Qty1
11/1/2003, 1, SampleText, Qty1
1/1/2004, 1, SampleText, Qty1
8/1/2004, 1, SampleText, Qty1

because the range for the graph is for 12 months, I created a variable
table that simply holds the 12 months begining w/ the earliest month in
this case that is August 2003.

I take my table in a very simplistic

SELECT FIELDS FROM PrimaryTable PT RIGHT OUTER JOIN @SecondaryTable ST
ON PT.DateField = ST.DateField

the output I expect back is:

8/1/2003
9/1/2003
10/1/2003
11/1/2003
12/1/2003
1/1/2004
2/1/2004
3/1/2004
4/1/2004
5/1/2004
6/1/2004
7/1/2004
8/1/2004
9/1/2004
10/1/2004
11/1/2004
12/1/2004


however I only get back the original subset w/ all the missing months :(
... does this make sense?


On Mon, 13 Dec 2004 12:56:25 -0800, Charlotte Foust
<cfoust at infostatsystems.com> wrote:
> If you have a date dimension table such as might be used in a 
> datawarehouse, you can simply join the two tables and pull the missing

> dates out of the date dimension table.
> 
> Charlotte Foust
> 
> 
> 
> 
> -----Original Message-----
> From: Francisco Tapia [mailto:fhtapia at gmail.com]
> Sent: Monday, December 13, 2004 10:01 AM
> To: dba-sqlserver at databaseadvisors.com
> Subject: [dba-SQLServer] Reports
> 
> perhaps someone here can help me out so this reporting goes a lot 
> smoother :)
> 
> Here is a sample snapshot of my data
> 
> 8/1/2003, 1, SampleText, Qty1
> 10/1/2003, 1, SampleText, Qty1
> 11/1/2003, 1, SampleText, Qty1
> 1/1/2004, 1, SampleText, Qty1
> 8/1/2004, 1, SampleText, Qty1
> 
> There is only data for these months... would you create a temp table 
> to fill in the rest of the months within the criteria?
> 
> ex: 8/1/2003 - 8/31/2004
> 
> --
> -Francisco
> http://pcthis.blogspot.com | PC news with out the jargon!



-- 
-Francisco
http://pcthis.blogspot.com | PC news with out the jargon!
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com