Conditional Expression in WHERE Clause

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 2005 Forums

Transact-SQL (2005)

Conditional Expression in WHERE Clause

Author

Topic

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-09-17 : 00:45:11

Hi Team,

I have the following scenario. I have given a working query also. However it uses the '12/31/9999' value. Is there any alternative for this ? Please help..

DECLARE @ApplicationStatus TABLE (ReqID INT, ApplicationDate SMALLDATETIME, ApplicationStatus VARCHAR(20))

INSERT INTO @ApplicationStatus (ReqID,ApplicationDate,ApplicationStatus) VALUES (1,'1/1/2010','In Progress')
INSERT INTO @ApplicationStatus (ReqID,ApplicationDate,ApplicationStatus) VALUES (2,'10/10/2010','In Progress')
INSERT INTO @ApplicationStatus (ReqID,ApplicationDate,ApplicationStatus) VALUES (3,'1/5/2010','Completed')
INSERT INTO @ApplicationStatus (ReqID,ApplicationDate,ApplicationStatus) VALUES (4,'1/5/2010','Cancelled')

DECLARE @MaxDate DATETIME
SET @MaxDate = '12/31/9999'

DECLARE @InputDateRange SMALLDATETIME
SET @InputDateRange = '5/5/2010'

-- All the 'Completed' inreespective of Application Date
-- All the 'Cancelled' inreespective of Application Date
-- Those 'In Progress' that have application date less than or equal to the input date.

-- Expected Output is ReqID 1,3,4

-- Solution that uses Maximum Date '12/31/9999'
SELECT *
FROM @ApplicationStatus
WHERE ApplicationDate <= CASE WHEN ApplicationStatus = 'In Progress' THEN @InputDateRange ELSE @MaxDate END

Thanks
Lijo

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-09-17 : 00:56:14

select * from @ApplicationStatus where (ApplicationStatus = 'In Progress' and ApplicationDate <= @InputDateRange) or ApplicationStatus <> 'In Progress'

Sachin.Nand

2937 Posts

Posted - 2010-09-17 : 03:05:22

OR
If you want to use case you query it like this


SELECT * 
FROM @ApplicationStatus
WHERE ApplicationDate <= CASE WHEN ApplicationStatus = 'In Progress' THEN @InputDateRange END or ApplicationStatus <> 'In Progress'

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-09-18 : 02:43:48

Thank you...

I prefer to avoid CASE when it is possible.

One question - is there any sceanrio that can be handled only by CASE expression in WHERE; there is no bit alternative ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources