How do I include all dates between 2 parameters in a table?

I'm working with a data set where some values have an aggretate total
(for example, a count of something) in a certain date range and others
do not. The problem I am having is that I need each page of the report
to show all the possible dates and the display a 0 when there are no
values for the date. Currently it is dropping that date.
For Example:
@.StartDate = 1/1/06
@.EndDate = 6/31/06
-- I get this on grouped values where there are empty months:
Jan06 5
Feb06 3
Apr06 7
Jun06 10
-- But I want this:
Jan06 5
Feb06 3
Mar06 0
Apr06 7
May06 0
Jun06 10
Due to other report properties and calculations, I can't really modify
my query to fix this problem. It seems like it should be easy to solve
but I'm not getting it!!!
Thanks.I think you must be using table control. so no way you can substitute 0 for
missing one. you need to modify query to use right join to include all the
rows.
if you hard code all the months value still you need to change the query to
get all the rows to column..
Try to change query, rather than breaking head.
Amarnath
"CanoAko" wrote:
> I'm working with a data set where some values have an aggretate total
> (for example, a count of something) in a certain date range and others
> do not. The problem I am having is that I need each page of the report
> to show all the possible dates and the display a 0 when there are no
> values for the date. Currently it is dropping that date.
> For Example:
> @.StartDate = 1/1/06
> @.EndDate = 6/31/06
> -- I get this on grouped values where there are empty months:
> Jan06 5
> Feb06 3
> Apr06 7
> Jun06 10
> -- But I want this:
> Jan06 5
> Feb06 3
> Mar06 0
> Apr06 7
> May06 0
> Jun06 10
> Due to other report properties and calculations, I can't really modify
> my query to fix this problem. It seems like it should be easy to solve
> but I'm not getting it!!!
> Thanks.
>|||Hmm... that doesn't really help. I admit now that I will probably have
to design a query to do it. The problem is that the query joins 2
tables and the date that I'm pulling is on the second table. So the
date doesn't exist to do an aggregate with a total of 0 in the first
place.
I'm going to try it with CASE to force it to happen, but I'm not too
excited about that. I've only been doing this for a month so I still
haven't figured everything out.
Amarnath wrote:
> I think you must be using table control. so no way you can substitute 0 for
> missing one. you need to modify query to use right join to include all the
> rows.
> if you hard code all the months value still you need to change the query to
> get all the rows to column..
> Try to change query, rather than breaking head.
> Amarnath
>
> "CanoAko" wrote:
> > I'm working with a data set where some values have an aggretate total
> > (for example, a count of something) in a certain date range and others
> > do not. The problem I am having is that I need each page of the report
> > to show all the possible dates and the display a 0 when there are no
> > values for the date. Currently it is dropping that date.
> >
> > For Example:
> >
> > @.StartDate = 1/1/06
> > @.EndDate = 6/31/06
> >
> > -- I get this on grouped values where there are empty months:
> > Jan06 5
> > Feb06 3
> > Apr06 7
> > Jun06 10
> >
> > -- But I want this:
> > Jan06 5
> > Feb06 3
> > Mar06 0
> > Apr06 7
> > May06 0
> > Jun06 10
> >
> > Due to other report properties and calculations, I can't really modify
> > my query to fix this problem. It seems like it should be easy to solve
> > but I'm not getting it!!!
> >
> > Thanks.
> >
> >|||Hi,
thats quite easy, use the following link to see how to generate a
calendar table, you can either persits it or create it on the fly.
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--