SQL tip please?

[berkshirea]

Hi guys, I got this table:

id datefield countfruits
1 5/1/2009 5
2 6/3/2009 2
3 7/2/2009 14
4 8/8/2009 8
5 9/3/2009 4
6 10/12/2009 3
7 11/21/2009 12
8 12/11/2009 20

and I want the recordset to look something like this below. I tried all sorts but can't seem to do it. Any tips? thanks a lot.

datefield countfruits
1/1/2009 0
2/1/2009 0
3/1/2009 0
4/1/2009 0
5/1/2009 5
6/3/2009 2
7/2/2009 14
8/8/2009 8
9/3/2009 4
10/12/2009 3
11/21/2009 12
12/11/2009 20

 

[SQLBill]

Start off by looking at the posts under the FAQs tab. There's a whole section on DATETIME tricks & tips.

Basically, what you would need is a Calendar table that just holds date values. This should be a list of the 1st of each month (1/1/2010, 2/1/2010, etc - but use a DATETIME column with dates that can't be misunderstood - 20100101).

Then have your query SELECT each month and count from your table and where the month is NULL, use the value from your Calendar table.

There's been enough posts about similar things, that you should be able to find help via a search if the FAQs don't help or if you get stuck.

-SQLBill



The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875