Filling out gaps in a date based select statement

Hi,
I have a query which simply returns a count of a particular field by w
number. My query as it works fine however for ws where there are no data
it obviously doesn't return a row. My problem is that is I wish for the
statement to return a zero for those ws with no data - I'm just not sure
how to do this.
I realise that I can achieve it by creating a table listing w numbers and
joining my query on that table. However I'd rather not create a table like
that. Is there another approach that will let me achieve the same thing?
Currently my query is:
SELECT datepart(wk, datein) AS Wno , COUNT(movementno) AS ContainersIn
STOCK stock
WHERE year(datein) = '2005'
GROUP BY datepart(wk, datein)
which returns:
W ContainersIn
29 3
31 1
34 5
35 4
36 8
37 6
38 3
39 1
40 12
Thanks,
Chris.See if this helps you
http://weblogs.sqlteam.com/jeffs/ar...09/12/7755.aspx
Madhivanan|||Why don't you want to create a table for this? I would recommend you
keep an auxiliary numbers table (a table integers from 0 to some
arbitrarily large number) for exactly such an application. A Calendar
table is also very useful to have around.
A SELECT statement can't create data out of nothing. Some other options
are: use a derived table in your query with a UNION of the numbers 1 -
52 ; create a view containing those numbers ; create a table-valued
function that returns a parameterized range of numbers ; extract the
numbers from system table(s) (not recommended). Creating the permanent
table would be my favourite - it's fast, easy, portable and much more
concise and transparent than any of the others.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1128332658.694445.44410@.g47g2000cwa.googlegroups.com...
> Why don't you want to create a table for this? I would recommend you
> keep an auxiliary numbers table (a table integers from 0 to some
> arbitrarily large number) for exactly such an application. A Calendar
> table is also very useful to have around.
> A SELECT statement can't create data out of nothing. Some other options
> are: use a derived table in your query with a UNION of the numbers 1 -
> 52 ; create a view containing those numbers ; create a table-valued
> function that returns a parameterized range of numbers ; extract the
> numbers from system table(s) (not recommended). Creating the permanent
> table would be my favourite - it's fast, easy, portable and much more
> concise and transparent than any of the others.
> --
> David Portas
> SQL Server MVP
> --
>
Thank your for your help David - most appreciated. I guess I find the idea
of creating a new table "messy". No idea why - just something that has
always struck me as something to avoid. A habit I will have to get out of!
thanks again
Chris.