[dba-SQLServer] Reports

Charlotte Foust cfoust at infostatsystems.com
Tue Dec 14 10:24:29 CST 2004


You should, yes.  I just find that syntax harder to read.

Charlotte Foust


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


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!
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com




More information about the dba-SQLServer mailing list