CODEWITHSUNDEEP
sqlt-sqlsql-server-2008
User_zyp9
User_zyp9

Reputation: 61

sql conditional WHERE clause CASE WHEN

I'm trying to subset a block of data for a year's time period. There are two date fields that I can use to subset. First, I should use DATE1 to use in my where clause. If that is NULL, then I need to use DATE2. If both are NULL, then obviously they wouldn't meet the condition.

NOTE: I'm not a SQL Server whiz. I'm just using Ent. Mgr to get some query results. I'm normally programming in SAS and am comfortable with TSQL, but I don't know all the tricks that come with SQL Server 2008.

SELECT count(CASE WHEN msg_fmt = 'ABC' then 1 END) as GRP1_CNT,
       count(CASE WHEN msg_fmt = 'LMN' then 1 END) as GRP2_CNT,
       count(CASE WHEN msg_fmt = 'XYZ' then 1 END) as GRP3_CNT,
FROM   msg_repository
WHERE  (CASE WHEN DATE1 IS NOT NULL then 
     DATE1 between '2010-01-01' and '2011-01-01'
    ELSE DATE2 between '2010-01-01' and '2011-01-01'
    END)
GROUP BY msg_fmt

So, if DATE1 isn't NULL, use that date. If it is NULL, use DATE2. I would prefer to have a solution that uses only T-SQL because the solution may be used in SAS also. But, I will take anything at the moment.

Thanks for any help.

Upvotes: 2

Views: 1751

Answers (2)

Chains
Chains

Reputation: 13157

If you're just dealing with two values, then ISNULL (good for 2 values) will work.

One caution I'd give if you're using a datetime data type, remember that a value like '2011-01-01' does not include any time (hours/minutes/seconds/nanoseconds) for that date. So in other words, 8am on '2011-01-01' is greater than '2011-01-01'.

Anyway, here's an example of ISNULL:

ISNULL(date1, date2) between '2010-01-01' and '2011-01-01'

Upvotes: 2

Andomar
Andomar

Reputation: 238086

You could use coalesce:

where coalesce(date1,date2) between '2010-01-01' and '2011-01-01'

Upvotes: 5

Related Questions