Using IF within SELECT and variables

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

Transact-SQL (2008)

Using IF within SELECT and variables

Author

Topic

dordor
Starting Member

5 Posts

Posted - 2011-07-28 : 06:12:36

Hi

I'm trying to construct an SQL query which returns results using several CASE statements. The CASE statements use dates from pre-set variables, as well as from the table, eg:

CASE when CREATED < @StartDate and (RESOLUTIONDATE >= @StartDate Or RESOLUTIONDATE is null)
then 1
else 0
end as Start

CREATED and RESOLUTION date are both columns from a table. However, a new requirement has come up where where the RESOLUTIONDATE now has to be determined based on a condition. There is also a COMPLETEDDATE column, and the above CASE needs to pick the earlier of the two (COMPLETEDDATE and RESOLUTIONDATE) and then perform the CASE statement based on that. So what I want is:

CASE when CREATED < @StartDate and ("the earlier date of RESOLUTIONDATE or COMPLETEDDATE" >= @StartDate Or RESOLUTIONDATE is null)
then 1
else 0
end as Start

How can I implement a query so that the earlier of the two dates is calculated prior to the CASE statement, and then that date is used in place of RESOLUTIONDATE?

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-28 : 06:27:04

You can rewrite the logic like this:

CASE when CREATED < @StartDate and 
(@StartDate < RESOLUTIONDATE or @StartDate < COMPLETEDDATE or RESOLUTIONDATE is null)

Alternatively, use a nested case expression like this:

CASE when CREATED < @StartDate and 
( case when RESOLUTIONDATE < COMPLETEDDATE then RESOLUTIONDATE else COMPLETEDDATE end >= @StartDate 
Or RESOLUTIONDATE is null)

dordor
Starting Member

5 Posts

Posted - 2011-07-28 : 06:57:54

Thanks :) I'll your nested expression method.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources