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,

Thanks

you 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 (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