How to consider another column in where condition if one column is null

Question

I have table like below,

Order_id      Created_Date       Approved_Date
_______________________________________________
1              2016-08-01         2016-08-05
2              2016-07-01         2016-07-04
3              2016-06-01         NULL


Select * from table1 where Approved_date between '2016-05-01' and '2016-08-31'

If the Approved_date is NULL, it should consider Created_date dynamically.

juergen d · Accepted Answer · 2016-08-12 09:52:08Z

1

Select * from table1 
where coalesce(Approved_date, Created_Date) between '2016-05-01' and '2016-08-31'

or to keep making use of indexes

Select * from table1 
where 
(
   Approved_date is not null 
   and Approved_date between '2016-05-01' and  '2016-08-31'
)
or 
(
   Approved_date is null 
   and Created_Date between '2016-05-01' and  '2016-08-31'
)

edited Aug 12, 2016 at 9:52

answered Aug 12, 2016 at 9:36

juergen d

205k40 gold badges304 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thorsten Kettner Over a year ago

You've mixed up the dates in your second query. (And I doubt that SQL Server would use an index for that query, but it may be worth a try. A pity that SQL Server doesn't support function indexes where you would simply index coalesce(approved_date,created_date).)

Tim Biegeleisen · Accepted Answer · 2016-08-12 09:37:36Z

0

Use COALESCE:

SELECT t1.Order_id,
       t1.Created_Date,
       COALESCE(t1.Approved_Date, t1.Created_Date)
FROM table1 t1
WHERE Approved_date BETWEEN '2016-05-01' AND '2016-08-31'

From the documentation:

Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.

In other words, in the above query Approved_Date will be used if it be not NULL, otherwise it was fallback to the Created_Date.

answered Aug 12, 2016 at 9:37

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Collectives™ on Stack Overflow

How to consider another column in where condition if one column is null

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related