Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Goalie35
Yak Posting Veteran
81 Posts |
 Posted - 2012-06-12 : 00:50:05
|
| Hello.I need to create a query that shows a count of orders, grouped by the week, for the entire year. So for example, I need something like this:Week | Total Orders------------------------Wk1: | 6Wk2: | 0Wk3 | 0Wk4: | 2Wk5: | 1etc...I'm able to get the values for weeks that contain values using this query:DATEPART(WEEK, CONVERT(varchar, o.OrderDate, 101)) as Weeks...however, my query doesn't return weeks with zero orders. Is there a way to return a count of orders, including zero counts, for all weeks within the current year?Thanks. |
|
|
vinu.vijayan
Posting Yak Master
227 Posts |
Posted - 2012-06-12 : 07:09:38
|
| Please post the DDL of the table and some Sample Data.N 28° 33' 11.93148"E 77° 14' 33.66384" |
 |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-06-12 : 07:38:49
|
| You are going to need a Tally Table (every one should have a Tally Table!) that has all the weeks in it,like thisDECLARE @Weeks TABLE (wk int)INSERT INTO @Weeksselect number from master..spt_values where type = 'p' and number < 54 and then left join that table to your target tableJimEveryday I learn something that somebody else already knew |
|
|
|
|
|
|
Group by week (including weeks with zero)