Francisco Tapia
fhtapia at gmail.com
Tue Dec 14 00:55:29 CST 2004
well I will try that tomorrow.. i worked around the issue by dumping the data to excel and then filling in the missing dates (nulls) flipping the tables around I ought to get the same results if I did a SELECT * FROM Primary Table "RIGHT OUTER JOIN" Variable Table Should I not? On Mon, 13 Dec 2004 16:21:18 -0800, Charlotte Foust <cfoust at infostatsystems.com> wrote: > Right, assuming you want all the dates in the variable table and only > > > matching records from the primary table. > > Charlotte Foust > > -----Original Message----- > From: Francisco Tapia [mailto:fhtapia at gmail.com] > Sent: Monday, December 13, 2004 3:38 PM > To: dba-sqlserver at databaseadvisors.com > Subject: Re: [dba-SQLServer] Reports > > 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! > _______________________________________________ > > > 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!