case statement in a view 1

[mrsbean]

I have written a view with a case statement. It returns records, but it doesnt work the way I thought it would.

SELECT     TempID, DateEntered, CountEntered, DateResolved, CountResolved, CASE DateEntered WHEN isnull(DateEntered, 1) 
                      THEN DateResolved ELSE DateEntered END AS TheDate
FROM         TempDailyReport
ORDER BY DateEntered, DateResolved

I wanted TheDate column to be equal to DateResolved when there was no value found in DateEntered. It leaves it blank every time. Can anybody tell me how to fix this?

A sample of the data returned is below:

TempID DateEntered CountEntered DateResolved CountResolved TheDate
153 BLANK BLANK 1/2/2005 1 BLANK
154 BLANK BLANK 1/26/2006 1 BLANK
155 BLANK BLANK 2/14/2006 1 BLANK
1 1/2/2006 10 1/2/2006 8 1/2/2006
2 1/3/2006 8 1/3/2006 8 1/3/2006

MrsBean

 

[SQLDenis]

No need for that do this instead

SELECT     TempID, DateEntered, CountEntered, DateResolved, CountResolved, 
COALESCE(DateEntered,DateResolved) AS TheDate
FROM         TempDailyReport
ORDER BY DateEntered, DateResolved

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/