|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
continuous datesI have a sql query that returns a count of a certain column say the number of bugs per week on a week by week basis. there are some weeks which have no bugs and these weeks are not returned. How can I write a sql query that has all the weeks whether there were bugs or not and return 0 for the weeks where there were no bugs. for example: at the moment: week no. of bugs 22/1/06 10 15/1/06 5 1/1/06 2 (week 8/1/06 is missing) what I expect : week no. of bugs 22/1/06 10 15/1/06 5 8/1/06 3 1/1/06 2 infact I want all the weeks from 1970 to present Thanking you, Regards, NJ You 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. -- Show quoteAndrew J. Kelly SQL MVP "NJ" <npau***@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 week on a week by week basis. there are some weeks > which have no bugs and these weeks are not returned. How can I write a > sql query that has all the weeks whether there were bugs or not and > return 0 for the weeks where there were no bugs. > > for example: > at the moment: > > week no. of bugs > 22/1/06 10 > 15/1/06 5 > 1/1/06 2 > (week 8/1/06 is missing) > > what I expect : > week no. of bugs > 22/1/06 10 > 15/1/06 5 > 8/1/06 3 > 1/1/06 2 > > infact I want all the weeks 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 week 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 week (starting date of the week 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 week) and counts all the rows with similar dates. ie: I group by week. Because of this there are weeks 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 week. 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 week 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 week (starting date of the week 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 week) and counts all the rows with similar dates. ie: I group by week. Because of this there are weeks 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 week. 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 WeekRanges (week_nbr INTEGER NOT NULL PRIMARY KEY, week_start DATETIME NOT NULL, week_finish DATETIME NOT NULL, CHECK (week_start, week_finish)); then do a query like this: SELECT W.week_nbr, W.week_start, COUNT(B.bug_id) FROM WeekRanges AS W LEFT OUT JOIN BugReport AS B ON B.bug_date BETWEEN W.week_start AND W, week_finish GROUP BY W.week_nbr, W.week_start; --CELKO-- wrote:
>Please post DDL, so that people do not have to guess what the keys, True. Unfortunately, not letting us see this code of yours>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. > > would have been much kinder. Slop, slop, slop. Show quote >Guessing at what you have, build a report range table: LEFT OUT JOIN?> >CREATE TABLE WeekRanges >(week_nbr INTEGER NOT NULL PRIMARY KEY, > week_start DATETIME NOT NULL, > week_finish DATETIME NOT NULL, > CHECK (week_start, week_finish)); > > CHECK what? >then do a query like this: > >SELECT W.week_nbr, W.week_start, COUNT(B.bug_id) > FROM WeekRanges AS W > LEFT OUT JOIN > > > BugReport AS B W, week_finish? And for that matter, how is BETWEEN going> ON B.bug_date BETWEEN W.week_start AND W, week_finish > > to work at all?. Do Sunday bugs get counted in two separate weeks, or do they not get counted at all? Who knows? Show quote >GROUP BY W.week_nbr, W.week_start; > > > Steve Kass Drew University On 21 Jan 2006 15:29:06 -0800, NJ wrote:
>Hi Guys, Hi NJ,> >I have a sql query that returns a count of a certain column say the >number of bugs per week on a week by week basis. there are some weeks >which have no bugs and these weeks are not returned. How can I write a >sql query that has all the weeks whether there were bugs or not and >return 0 for the weeks where there were no bugs. 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  |
|||||||||||||||||||||||
Other interesting topics