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

Missing system stored procedures

When I create a new database, the system stored procedures are missing;
dt_addtosourcecontrol_u
dt_checkinobject_u
etc.
I'm logged in as sa.Is that the only 2?

I've never used them before...but the appear in all the databases I've created.

What version are you using?

And can you see them in master?|||Those procedures are magically created by Enterprise Manager, as far as I can tell. Even if you are logged into EM as someone with no create procedure rights. I would not worry over them, unless you have errors. You may need to apply a service pack to the client, at worst.|||Whatdya know...I gotta script that for often...it's the only thing I don't script

CREATE DATABASE [myDB99]
ON (NAME = N'myDB99'
, FILENAME = N'd:\database\njros1d151dev\MSSQL$NJROS1D151DEV\da ta\myDB99.mdf'
, SIZE = 209, FILEGROWTH = 10%)
LOG ON (NAME = N'myDB99_log', FILENAME = N'd:\database\njros1d151dev\MSSQL$NJROS1D151DEV\da ta\myDB99.ldf'
, SIZE = 61
, FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO|||These procs are created automagically when you use the database diagrams feature in EM. These procedures have something to do with the creation of the diagrams (though i do not exactly know what).

I would not worry about them ... i have not ever seen anybody use them ... except the Enterprise Manager.|||I thought they had more to do with Visual Source Safe then with the DB Diagram feature...