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
--

How do I get the row number?

I have tryed to find a way to include the row number in a querry but without result.

I have a table select some rows ordered and now I will add a column called rank

How do I get the rownumber in the field rank ?

Thanks in advance

Jan OThe way I'd suggest assigning a row number to your resultset is to INSERT your resultset into a temporary table or table variable which has an IDENTITY column, something like this:

-- set up a table variable to hold the resultset
DECLARE @.myTable (Rank int identity(1,1) primary_key,
 ColumnA varchar(20),
 ColumnB varchar(20)
 )
-- insert the resultset into the table variable
INSERT INTO
 @.myTable
(
 ColumnA,
 ColumnB
)
SELECT
 ColumnA,
 ColumnB
FROM
 someTable
ORDER BY
 someCriteria
-- return the resultset to the calling program in rank order, including the rank column
SELECT
 Rank,
 ColumnA,
 ColumnB
FROM
 @.myTable
ORDER BY
 Rank

Terri|||Thanks,

Have hoped not to do it so, a lot of other calculation dependences.

Jan|||Well, an alternative (which performs poorly) would be at the very bottom of this link:Returning a Row Number in a Query It involves using a subquery for every row in your resultset and could be horrendous performance-wise.

Can you just assign the rank in the front end?

Terri|||I agree with Terri. If possible, assign in the front end. I have used the temp table solution before, as well. It really depends on why you need to have the rownumber. If it is just for display purposes, it should be no problem to create on the front end when binding your resultset.|||Thanks again.

I have started to solve the "problem" in the front end.
I have a datagrid with paging so I have a few lines left.
Got it working whitout paging , but I had prefered asystem rownr from SQL.

But you cant get everything for free :)

Thanks
Jan