Francisco Tapia
fhtapia at gmail.com
Tue Dec 14 15:48:24 CST 2004
the selection must be done at the server side because there will be "Reporting Services" So I wen this route.... --13 Month Selection DECLARE @rptMonths TABLE(rptMonth DateTime) INSERT INTO @rptMonths(rptMonth) VALUES(DATEADD(mm, 0, @StartDate)) INSERT INTO @rptMonths(rptMonth) VALUES(DATEADD(mm, 1, @StartDate)) INSERT INTO @rptMonths(rptMonth) VALUES(DATEADD(mm, 2, @StartDate)) INSERT INTO @rptMonths(rptMonth) VALUES(DATEADD(mm, 3, @StartDate)) INSERT INTO @rptMonths(rptMonth) VALUES(DATEADD(mm, 4, @StartDate)) INSERT INTO @rptMonths(rptMonth) VALUES(DATEADD(mm, 5, @StartDate)) INSERT INTO @rptMonths(rptMonth) VALUES(DATEADD(mm, 6, @StartDate)) INSERT INTO @rptMonths(rptMonth) VALUES(DATEADD(mm, 7, @StartDate)) INSERT INTO @rptMonths(rptMonth) VALUES(DATEADD(mm, 8, @StartDate)) INSERT INTO @rptMonths(rptMonth) VALUES(DATEADD(mm, 9, @StartDate)) INSERT INTO @rptMonths(rptMonth) VALUES(DATEADD(mm, 10, @StartDate)) INSERT INTO @rptMonths(rptMonth) VALUES(DATEADD(mm, 11, @StartDate)) INSERT INTO @rptMonths(rptMonth) VALUES(DATEADD(mm, 12, @StartDate)) On Tue, 14 Dec 2004 11:23:34 -0800, Charlotte Foust <cfoust at infostatsystems.com> wrote: > 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 > > _______________________________________________ > 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!