Missing Values

Hello people,

I have been writing some queries and reports using SQL (with Crystal and ASP.NET), and have a problem. Something I often need to do is find the number of records for each month, so there is a date field and a currency field, and I need to sum all the currency values for each month.

Firstly, to get a group for each month of each year I am using ...GROUP BY Month(DateField), Year(DateField)
is this correct or is there a better way?

The more problematic thing is, where there are no records for certain month, of course this month does not show up in the query results. This is a problem when displaying results in a table/chart. Is there any way to force each month to appear (with a 0 in the SUMed field)?

Any advice would be most, most welcome,

Thanksyou could define a table say tblmonth and define a join, which will allways retrieve the a month even if there are no entries for that month|||I'll have a go at that, thanks

any idea what join that would be?|||I think it would be a left join (Outer joins allow select all from this side and show any matching on the other).

Give it a go and if what you get is silly switch to right join as there is a small chance I got it wrong.|||you'll need a table of months, or use an integers table to generate them

see Selecting all months even if they're missing (http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid526288_tax285649,00.html) (site registration may be required, but it's free)|||Thanks for your help on this, I seem to have it working now.

I have a similar problem with a report I am writing in crystal reports. I am trying to create a group calendar from Outlook using a crosstab, but not all dates show up (if noone has an appointment booked on that day).

What I need is to do is get a datasource with a list of days which I can do a join on to force all dates to appear. The problem is that the dates from Outlook are just dates but other applications such as access, excel use date/time fields and so the join does not work! Does anyone know where I can get a data source of just dates to use in the join?|||a data source of just dates? yeah, you can generate it from an integers table