[dba-SQLServer] Reports

Charlotte Foust cfoust at infostatsystems.com
Tue Dec 14 13:23:34 CST 2004


Build yourself a Dates table and include it in your projects by default.
You could even do it in XML and just read it when you need it.  It
simplifies a lot of reporting.

Charlotte Foust


-----Original Message-----
From: Francisco Tapia [mailto:fhtapia at gmail.com] 
Sent: Tuesday, December 14, 2004 10:24 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Reports


:),  well I originally didn't want to include the variable table (more
typing ;)) but it looks like this is the method I must go with.  I
really just wanted an opinion on what people would choose, the variable
table or if there was an easier or Better way.


On Tue, 14 Dec 2004 08:24:29 -0800, Charlotte Foust
<cfoust at infostatsystems.com> wrote:
> 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
> 
> _______________________________________________
> 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