Counting recordsets with no rows returned

[Garabaldi]

I am currently pulling a recordset from a sql database. I am using the SQL Server 7 count function to count each record that is being pulled.

There are currently four criteria I am looking for. The data is grouped by date. If no rows are retruned then I need the count to return a 0. I need to know if this is possible. Right now, it is only returning dates with data in it.

The following is an example:

Select
date,
count (*) as Numbers

from
table 1
inner join table 2
on number

where
date between @startdate and @enddate and
territory = @territory and
code = @code and
org = @org

group by date, territory, code, org
order by date, territory, code, org

I need to know if there is any possible way to correct this in SQL without using external code. Any suggestions would be really appreciated.

 

[JamesLean]

Change your join to a LEFT JOIN rather than INNER.

--James

 

[JamesLean]

Oh, also you only need to GROUP BY date if that's all you're showing.

--James

 

[Garabaldi]

I've tried that... its not working. What is happening is when those four criteria aren't met its just skipping that row. I need to it come back as 0 for that date.

Hope this makes it a little clearer.

Thanks

 

[pascalsql]

If there is NO record with a specific date, say june 9 2003, sql server cannot invent it.

You need a table containing all the dates. This can be done with a utility calendar table that is left joined or with a sub-query.

select calendar_date,
(select count(*) from xxx where date = calendar_date)
from calendar

 

[Garabaldi]

Thanks very much for your help. It worked!!!