Query to bring back every day of month

[Gill1978]

Hi All,

I have a query that brings back a count of a field in a table grouped by Process_Date.

This brings back the right count but if for some reason nothings processed on a particular day of that month it doesn't bring back a data row for that day. What I would like is for the query to bring back a row per day of that month ... but I can't figure out how.

This is what I have so far:

Declare @Start as datetime
Declare @End as datetime 
Declare @dateCounter as datetime

Set @Start = '01/07/2007'
Set @End = '31/07/2007'
Set @dateCounter = @Start

while @dateCounter !> @End
begin
	
	select Process_Date, count(tag20c_corp) from v_MT568_Outgoing_1
	where Process_Date = @dateCounter
	group by Process_Date
	set @dateCounter = @dateCounter + 1

end

This gives me 30 select statements ... how do I get it to bring me back a table with 30 rows instead??

Can you help?

Thanks

Julie

 

[Crowley16]

erm, are you normally a c# programmer or or something like that?

try doing:

select Process_Date, count(tag20c_corp)
from v_MT568_Outgoing_1
where Process_Date between @Start and @End
group by Process_Date


--------------------
Procrastinate Now!

 

[Gill1978]

I think maybe I didn't explain myself properly..

Because of the data ... for the a particular month you may records for 3 days of the month so if I used the query above I'd get 3 records in the select statemnt.

However I'd like the sql statement to bring back 30 records (one record for every day of the month) so it'd look something like this

Process_Date tag20c_corp
-------------------------
01/12/2007 8
02/12/2007 0
03/12/2007 0
04/12/2007 12

etc ....

Is that a bit clearer??

 

[Crowley16]

I see,

I'd setup a table to put in all the dates in the month, and then use a left join on that table, perhaps with a coalesce on the fields as well...

you can use a table variable or temp table if you don't want to persist the table.

--------------------
Procrastinate Now!