Problem with Date critieria - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Problem with Date critieria

Author

Topic

ckuo@kahluadesigns.com
Yak Posting Veteran

58 Posts

Posted - 2003-02-25 : 00:37:45

Hi,
I'm having problems with the date critieria. If i take out this line
Projects.StartDate >= Convert( smalldatetime, '1/1/2002') AND Projects.EndDate <= Convert( smalldatetime, '12/31/2002'))

the rest of the criterias work, if i leave it in, the critieria arent used as filters. Seems like when I change this 'OR (ProjectDemographics...' to a 'AND (ProjectDemographics...." then the date criteria works. Any clue on how to get my where clauses to work?
Thanks

SELECT DISTINCT BudgetDetails.TotalFundsRequested, Projects.StartDate, Projects.EndDate, Projects.GeneralComplete, Projects.BudgetComplete, Projects.ParameterComplete, Projects.ProjectID, Projects.Title, Projects.Summary, Projects.DateAdded, Projects.PartialFunding
FROM Projects
INNER JOIN BudgetDetails ON Projects.ProjectID = BudgetDetails.ProjectID
INNER JOIN ProjectDemographics ON Projects.ProjectID = ProjectDemographics.ProjectID
INNER JOIN ProjectSectorInterests ON Projects.ProjectID = ProjectSectorInterests.ProjectID
INNER JOIN ProjectCounties ON Projects.ProjectID = ProjectCounties.ProjectID
WHERE
(Projects.GeneralComplete = 1 AND Projects.BudgetComplete = 1 AND Projects.ParameterComplete = 1)
AND (BudgetDetails.TotalFundsRequested BETWEEN 9999 AND 500000 )
AND (Projects.PartialFunding Is NULL)
AND (ProjectCounties.County = 'LOS ANGELES' OR ProjectCounties.County = 'ORANGE')
AND (Projects.StartDate >= Convert( smalldatetime, '1/1/2002') AND Projects.EndDate <= Convert( smalldatetime, '12/31/2002'))
OR (ProjectDemographics.DemographicID IN(10, 11, 12, 22, 17, 19 ) OR ProjectSectorInterests.SectorInterestID IN(3,2,1 ))
ORDER BY Projects.DateAdded DESC, Projects.Title

nr
SQLTeam MVY

12543 Posts

Posted - 2003-02-25 : 00:52:11

WHERE
(
Projects.GeneralComplete = 1
AND Projects.BudgetComplete = 1
AND Projects.ParameterComplete = 1
AND BudgetDetails.TotalFundsRequested BETWEEN 9999 AND 500000
AND Projects.PartialFunding Is NULL
AND (ProjectCounties.County = 'LOS ANGELES' OR ProjectCounties.County = 'ORANGE')
AND Projects.StartDate >= Convert( smalldatetime, '1/1/2002')
AND Projects.EndDate <= Convert( smalldatetime, '12/31/2002')
)
OR
(ProjectDemographics.DemographicID IN(10, 11, 12, 22, 17, 19 ) OR ProjectSectorInterests.SectorInterestID IN(3,2,1 ))
ORDER BY Projects.DateAdded DESC, Projects.Title

This is what you have.
I suspect that you need to put some brackets to associate the or clauses.
As it stands if ProjectSectorInterests.SectorInterestID = 3 it will ignore all the other filtering.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ckuo@kahluadesigns.com
Yak Posting Veteran

58 Posts

Posted - 2003-02-25 : 10:51:59

So how would i get it so that the first set of criterias are needed, then after the OR, those IN Clauses are optional?

nr
SQLTeam MVY

12543 Posts

Posted - 2003-02-25 : 11:36:01

No such thing as optional, still not sure what you want.

What you have is if a record fulfills any of the conditions in the or clauses at the end then it is returned.
If it doesn't fulfill those conditions but fulfills all of the and conditions then it is returned.

consider

where
(
a in (1,2)
and a in (2,3)
)
or
(a = 4 or a = 5)

this will return records for a = 2, 4, 5

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources