show date even when no rows selected

[Rodiggy]

Hello,

I have a query that looks like this:

select convert(varchar(12),querydatetime,101) as date,
count(uniquefieldId)

from CMquery

where datepart(month,querydatetime)=datepart(month,getdate())-1
and (majorTrigger=45 or thisfield='aaaa')

group by convert(varchar(12),querydatetime,101)

order by convert(varchar(12),querydatetime,101)


It returns results that look like this

07/02/2001 15
07/05/2001 45
07/07/2001 12
etc etc.

What I want to know is if there is a way to get it return all the days even those with counts of zero, so the results would look like

07/01/2001 0
07/02/2001 15
07/03/2001 0
07/04/2001 0
07/05/2001 45
etc etc

Does anyone know how to do this?

Thanks

 

[tlbroadbent]

Create a dates table. Insert a record for each date in the table. Join that table with the data source in your query.

select convert(varchar(12),datecol,101) as date, count(uniquefieldId)
from DatesTable Left Join CMquery
on datestable.datecol=CMqueery.querydatetime
where datepart(month,datestable.datecol) = datepart(month,getdate())-1
and (majorTrigger=45 or thisfield='aaaa')
group by convert(varchar(12),datecol,101)
order by convert(varchar(12),datecol,101)
Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it if you have time.