SQL datediff from different columns-CodePudding

I have a table that has a date of action (let's call it X) either in a field or another (depending on another variable), the problem is that I need to get the date of when 'X' happened, so I need a new column that has the value of column1 or column2, depending on where the date is.

I am super green with this, so I've been trying with different things and now with CASE.

I got the below, but all the values I am getting in the new column "DATE_X_FINAL" all values NULL.

Even though I need a new column, I am trying with select now just to see if I can get it working.

Any ideas?

SELECT [Date_X], [Date_X2], 
       CASE 
       WHEN [Date_X] = NULL THEN [Date_X2]
       END as DATE_X_FINAL
    FROM [DATABASE.TABLE]

Result:

[Date_X]    [Date_X2]   DATE_X_FINAL
2020-12-23   NULL       NULL
2019-02-05   NULL       NULL
2019-02-13   NULL       NULL
2019-02-12   NULL       NULL
2019-02-06   NULL       NULL

CodePudding user response：

In SQL null is not equal (=) to anything—not even to another null.

You test for null comparison by using IS NULL.

There is a built in convenience function to do this in standard (ANSI) SQL - COALESCE. ("The COALESCE() function returns the first non-null value in a list.")

SELECT Date_X, Date_X2
     , COALESCE(Date_X, Date_X2) AS 'DATE_X_FINAL' 
FROM DATABASE.TABLE