continuous dates

Hi Guys,
I have a sql query that returns a count of a certain column say the
number of bugs per w on a w by w basis. there are some ws
which have no bugs and these ws are not returned. How can I write a
sql query that has all the ws whether there were bugs or not and
return 0 for the ws where there were no bugs.
for example:
at the moment:
w no. of bugs
22/1/06 10
15/1/06 5
1/1/06 2
(w 8/1/06 is missing)
what I expect :
w no. of bugs
22/1/06 10
15/1/06 5
8/1/06 3
1/1/06 2
infact I want all the ws from 1970 to present
Thanking you,
Regards,
NJYou need to provide more information to get an accurate response. It would
greatly help to see the table involved and some sample data. What is the
criteria for determining if there is a bug or not? Are you summing rows
with similar dates or is there a BUG column that has a value in it. Your
example does not make sense in that you state 8/1/06 is missing yet you show
it as having 3 bugs.
Andrew J. Kelly SQL MVP
"NJ" <npaulus@.hotmail.com> wrote in message
news:1137886146.073541.14890@.g43g2000cwa.googlegroups.com...
> Hi Guys,
> I have a sql query that returns a count of a certain column say the
> number of bugs per w on a w by w basis. there are some ws
> which have no bugs and these ws are not returned. How can I write a
> sql query that has all the ws whether there were bugs or not and
> return 0 for the ws where there were no bugs.
> for example:
> at the moment:
> w no. of bugs
> 22/1/06 10
> 15/1/06 5
> 1/1/06 2
> (w 8/1/06 is missing)
> what I expect :
> w no. of bugs
> 22/1/06 10
> 15/1/06 5
> 8/1/06 3
> 1/1/06 2
> infact I want all the ws from 1970 to present
> Thanking you,
> Regards,
> NJ
>|||Thanks for yor reply Andrew
I am summing rows with similar dates. and sorry about the 8/1/06 too it
should have 0 bugs. It doesnt show in the output of the first query
because there are no bugs during the w 8/1/06 to 14/1/06. In my
table everytime there is a bug it is entered into the database along
with the date it happened.
my query then finds the w (starting date of the w when the bug
occured i.e if the bug occoured on the 11 th jan 2006 then it becomes
8/1/06 as 8th is the starting day of the w) and counts all the rows
with similar dates. ie: I group by w. Because of this there are
ws when no bugs occured and hence dont show up in the query result.
however I want to be able to return that date with a 0 for the number
of bugs for that w.
I hope that explains a bit better Andrew.
Regards,
NJ|||Thanks for yor reply Andrew
I am summing rows with similar dates. and sorry about the 8/1/06 too it
should have 0 bugs. It doesnt show in the output of the first query
because there are no bugs during the w 8/1/06 to 14/1/06. In my
table everytime there is a bug it is entered into the database along
with the date it happened.
my query then finds the w (starting date of the w when the bug
occured i.e if the bug occoured on the 11 th jan 2006 then it becomes
8/1/06 as 8th is the starting day of the w) and counts all the rows
with similar dates. ie: I group by w. Because of this there are
ws when no bugs occured and hence dont show up in the query result.
however I want to be able to return that date with a 0 for the number
of bugs for that w.
I hope that explains a bit better Andrew.
Regards,
NJ|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Guessing at what you have, build a report range table:
CREATE TABLE WRanges
(w_nbr INTEGER NOT NULL PRIMARY KEY,
w_start DATETIME NOT NULL,
w_finish DATETIME NOT NULL,
CHECK (w_start, w_finish));
then do a query like this:
SELECT W.w_nbr, W.w_start, COUNT(B.bug_id)
FROM WRanges AS W
LEFT OUT JOIN
BugReport AS B
ON B.bug_date BETWEEN W.w_start AND W, w_finish
GROUP BY W.w_nbr, W.w_start;|||
--CELKO-- wrote:

>Please post DDL, so that people do not have to guess what the keys,
>constraints, Declarative Referential Integrity, data types, etc. in
>your schema are. Sample data is also a good idea, along with clear
>specifications. It is very hard to debug code when you do not let us
>see it.
>
True. Unfortunately, not letting us see this code of yours
would have been much kinder. Slop, slop, slop.

>Guessing at what you have, build a report range table:
>CREATE TABLE WRanges
>(w_nbr INTEGER NOT NULL PRIMARY KEY,
> w_start DATETIME NOT NULL,
> w_finish DATETIME NOT NULL,
> CHECK (w_start, w_finish));
>
CHECK what?

>then do a query like this:
>SELECT W.w_nbr, W.w_start, COUNT(B.bug_id)
> FROM WRanges AS W
> LEFT OUT JOIN
>
LEFT OUT JOIN?

> BugReport AS B
> ON B.bug_date BETWEEN W.w_start AND W, w_finish
>
W, w_finish? And for that matter, how is BETWEEN going
to work at all?. Do Sunday bugs get counted in two separate
ws, or do they not get counted at all? Who knows?

>GROUP BY W.w_nbr, W.w_start;
>
>
Steve Kass
Drew University|||On 21 Jan 2006 15:29:06 -0800, NJ wrote:

>Hi Guys,
>I have a sql query that returns a count of a certain column say the
>number of bugs per w on a w by w basis. there are some ws
>which have no bugs and these ws are not returned. How can I write a
>sql query that has all the ws whether there were bugs or not and
>return 0 for the ws where there were no bugs.
Hi NJ,
Use a calendar table (www.aspfaq.com/2519).
SELECT c.dt, COUNT(y.BugDate)
FROM Calendar AS c
LEFT OUTER JOIN YourTable AS y
ON y.BugDate >= c.dt
AND y.BugDate < DATEADD(day, 7, c.dt)
WHERE c.dayname = 'Sunday'
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP