Data Manipulation

[adeelahmed]

Hi i want to get records between 2 years month wise, order by month

which should show records like

total : thisdate
----------------
50 : may 01
60 : dec 01
50 : jan 02

etc.

im using this query but, i want if there is no data in any month within defined range it should show "0" in that month field.
at this time it is showing records which has only data in it.


select count(*) total, datename(month,[date]) as thisdate
from prof_midt
where [date] between '1/1/2003' and '2/1/2005'
group by ([date])
order by [date]


Adeel.

[simonm]

You need to create a temporary table and populate it (with a cursor) with a record for every month in your date range. Then do a left outer join from it to the results of your query.

p.s. Your question should be in the databases forum really...