Coalesce and CASE

I'd like to expand a coalesce function to account for Case 3, which says tha
t
if Date2 is null, then stay with null. As you see the following only
satisfies Case 1 and Case 2. Any ideas appreciated. I think that a fancy
CASE statement might do it, but I can't figure it out. Thanks, --Bob
Select COALESCE(Date1, Date2) as DateX From tableXYZ
Desired
output
Date1 Date2 DateX
Case 1 11/08/05 11/14/05 11/08/05
Case 2 null 11/14/05 11/14/05
Case 3 11/08/05 null nullSELECT CASE WHEN date2 IS NULL
THEN CAST( NULL AS DATETIME)
ELSE COALESCE (date1, date2) END
AS date_x
FROM Foobar;
The CAST() can be important in this kind of CASE expression.|||SELECT 'DateX'=
CASE
WHEN Date2 IS NULL THEN NULL
WHEN Date1 IS NULL THEN Date2
ELSE Date1
END
FROM TableXYZ
This works if you always want Date1 returned for the value of DateX when
neither are NULL.
"BobS" wrote:

> I'd like to expand a coalesce function to account for Case 3, which says t
hat
> if Date2 is null, then stay with null. As you see the following only
> satisfies Case 1 and Case 2. Any ideas appreciated. I think that a fancy
> CASE statement might do it, but I can't figure it out. Thanks, --Bob
> Select COALESCE(Date1, Date2) as DateX From tableXYZ
> Desired
> output
> Date1 Date2 DateX
> Case 1 11/08/05 11/14/05 11/08/05
> Case 2 null 11/14/05 11/14/05
> Case 3 11/08/05 null null
>
>