Missing zeros

I'm trying to report on the number of tickets created by day for 21 days, but
when there are no tickets logged on a day, i need a 0. I have been trying to
get my query to return 0's for every day of the 21 days, but that hasn't
worked. I have tried to get the Matrix to show 21 days, but that hasn't
worked either. To add complexity, i need to graph this matrix, so i cannot
simply use a sub-report. (otherwise it would be done.)
A subset of the results from the query are as follows:
1/5/2006 4
1/6/2006 5
1/8/2006 3
1/20/2006 1
What i need is:
1/4/2006 0
1/5/2006 4
1/6/2006 5
1/7/2006 0
1/8/2006 3
1/9/2006 0
1/20/2006 1
Back in my access days, i just ran the maketable query, then used VB to
'pad' in as many zero values as needed, and then ran the report off the table.
Any suggestions?Where do you get the data from? Where does the numbers come from - are you
summing in your query or is that done automagically somewhere in the
database? And where are the dates and the numbers connected?
My guess is that you have to make your SQL query return all 21 lines. If you
use an outer join for dates and numbers, and do a Case test for the numbers,
you should get all your rows.
Kaisa M. Lindahl
"Brent Maloney" <BrentMaloney@.discussions.microsoft.com> wrote in message
news:7C5F489A-68D9-4681-88B9-324B77104AC7@.microsoft.com...
> I'm trying to report on the number of tickets created by day for 21 days,
> but
> when there are no tickets logged on a day, i need a 0. I have been trying
> to
> get my query to return 0's for every day of the 21 days, but that hasn't
> worked. I have tried to get the Matrix to show 21 days, but that hasn't
> worked either. To add complexity, i need to graph this matrix, so i
> cannot
> simply use a sub-report. (otherwise it would be done.)
> A subset of the results from the query are as follows:
> 1/5/2006 4
> 1/6/2006 5
> 1/8/2006 3
> 1/20/2006 1
> What i need is:
> 1/4/2006 0
> 1/5/2006 4
> 1/6/2006 5
> 1/7/2006 0
> 1/8/2006 3
> 1/9/2006 0
> 1/20/2006 1
> Back in my access days, i just ran the maketable query, then used VB to
> 'pad' in as many zero values as needed, and then ran the report off the
> table.
> Any suggestions?|||If there are no rows for days with no tickets and you are using a query like..
select date, sum(ticketsales) from table group by date
you will get no rows for dates with no tickets... One way to do this would
be to create another table with one for each date /... Then your query could
be
select dt.date, sum(isnull(ticketsales,0)) from table inner join dttable dt
on dt.date = table.dt group by dt.date
You will get a row for each date...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Kaisa M. Lindahl" wrote:
> Where do you get the data from? Where does the numbers come from - are you
> summing in your query or is that done automagically somewhere in the
> database? And where are the dates and the numbers connected?
> My guess is that you have to make your SQL query return all 21 lines. If you
> use an outer join for dates and numbers, and do a Case test for the numbers,
> you should get all your rows.
> Kaisa M. Lindahl
> "Brent Maloney" <BrentMaloney@.discussions.microsoft.com> wrote in message
> news:7C5F489A-68D9-4681-88B9-324B77104AC7@.microsoft.com...
> > I'm trying to report on the number of tickets created by day for 21 days,
> > but
> > when there are no tickets logged on a day, i need a 0. I have been trying
> > to
> > get my query to return 0's for every day of the 21 days, but that hasn't
> > worked. I have tried to get the Matrix to show 21 days, but that hasn't
> > worked either. To add complexity, i need to graph this matrix, so i
> > cannot
> > simply use a sub-report. (otherwise it would be done.)
> >
> > A subset of the results from the query are as follows:
> > 1/5/2006 4
> > 1/6/2006 5
> > 1/8/2006 3
> > 1/20/2006 1
> >
> > What i need is:
> > 1/4/2006 0
> > 1/5/2006 4
> > 1/6/2006 5
> > 1/7/2006 0
> > 1/8/2006 3
> > 1/9/2006 0
> > 1/20/2006 1
> >
> > Back in my access days, i just ran the maketable query, then used VB to
> > 'pad' in as many zero values as needed, and then ran the report off the
> > table.
> >
> > Any suggestions?
>
>