[dba-SQLServer] Reports

Francisco Tapia fhtapia at gmail.com
Mon Dec 13 17:37:33 CST 2004


so if I'm understanding you correctly it should be

Select * From VariableTable LEFT OUTER JOIN PrimaryTable ....



On Mon, 13 Dec 2004 14:56:14 -0800, Charlotte Foust
<cfoust at infostatsystems.com> wrote:
> 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
> 
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
> 
> 


-- 
-Francisco
http://pcthis.blogspot.com | PC news with out the jargon!



More information about the dba-SQLServer mailing list