Date ranges and an SQL query

[Dross]

I based my report on the query below. I created 2 parameters called startdate and enddate. I want to get the state, franchisee stores and company stores that were open when I enter the date ranges. I guess I am supposed to use the selection expert to do this but I can't get it to work. I keep getting all the stores. Even the ones that are not in that date range. Does anyone know how to do this?

Declare @startdate datetime, @enddate datetime
Select @startdate = '4/1/2002'
Select @enddate = '4/30/2002'
Select Distinct StateProvince, StateProvinceName,
(select Count(distinct officeid) from office inner join territoryhistory on office.territoryid =
territoryhistory.territoryid where territoryhistory.statuscode = '1' and archivedate between @startdate and @enddate
and office.statuscode in ('1', '3') and office.StateProvince = stateprovince.StateProvince) as 'Company Stores',
(select Count(distinct officeid) from office inner join territoryhistory on office.territoryid =
territoryhistory.territoryid where territoryhistory.statuscode = '3' and archivedate between @startdate and @enddate
and office.statuscode in ('1', '3') and territoryhistory.stateprovince = stateprovince.stateprovince) as 'Franchisee Stores'
from stateprovince where countrycode = 'U' order by stateprovince

 

[kolcun]

Which database are you using, if its a Microsoft Access DB, you need to use the format #mm/dd/yyyy# for your dates

hope this helps

mike kolcun
mike@xminus1.com

 

[lyanch]

I would start with your inner selects. Check to make certain that they bring back the correct numbers when separated out. Just put in a "hard coded" stateprovince.StateProvince and start and end dates. Its always easier to approach these from the bottom up.

Another idea... the way you have this written above, it appears that no matter what you send as parameters the dates will be reset to the values shown above.

Lisa