CASE in WHERE Statement - 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

General SQL Server Forums

New to SQL Server Programming

CASE in WHERE Statement

Author

Topic

bielen
Yak Posting Veteran

97 Posts

Posted - 2008-10-24 : 11:14:38

I'm trying to have a conditional case expression where if the releaseType is "D" I just filter on the releaseType of "D". However, if it is "M", I want to filter on the releaseType of "M" and and start and end date for RptDate

SELECT QA_MaintOpenByState.Status, SUM (QA_MaintOpenByState.PTDPriority_1_Urgent) AS Priority1, SUM(QA_MaintOpenByState.PTDPriority_2_High) AS Priority2, SUM(QA_MaintOpenByState.PTDPriority_3_Medium) AS Priority3, SUM(QA_MaintOpenByState.PTDPriority_4_Low) AS Priority4, CASE WHEN status = 'Open' THEN 1 WHEN status = 'Design' THEN 2 WHEN status = 'Design' THEN 2 WHEN status = 'Dev' THEN 3 WHEN status = 'Dev' THEN 4 ELSE 5 END AS orderSequence
FROM QA_MaintOpenByState LEFT OUTER JOIN QA_Product_Groups ON QA_MaintOpenByState. PRODUCT = QA_Product_Groups.productName COLLATE database_default WHERE QA_Product_Groups.groupName = 'Facets') AND (QA_MaintOpenByState.WeekEndingDate = '10/28/2008') AND
(QA_MaintOpenByState.Status <> 'Others') AND

CASE WHEN (QA_MaintOpenByState.ReleaseType = 'D')
THEN (QA_MaintOpenByState.ReleaseType = 'D') END
CASE WHEN (QA_MaintOpenByState.ReleaseType = 'M')
THEN (QA_MaintOpenByState.ReleaseType = 'M') AND rptDate BETWEEN '4/1/08' AND '10/31/08' END

GROUP BY QA_MaintOpenByState.Status
ORDER BY orderSequence

I can't get the syntax correct for the CASE expression. Is this the best approach?

Thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-10-24 : 11:50:13

Try this:


WHERE 
       QA_Product_Groups.groupName = 'Facets') 
  AND (QA_MaintOpenByState.WeekEndingDate = '10/28/2008') 
  AND (QA_MaintOpenByState.Status <> 'Others')
  AND 
	(
		(
			 QA_MaintOpenByState.ReleaseType = 'D'
		)

	 OR

		(
			  QA_MaintOpenByState.ReleaseType = 'M'
		 and  rptDate BETWEEN '4/1/08' AND '10/31/08'
		)

   )

Jim

bielen
Yak Posting Veteran

97 Posts

Posted - 2008-10-24 : 17:29:26

Jim:

Thanks for your help. I have the script working.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources