PostgreSQL case statement for mutliple date fields

Question

I'm presenting a query wherein I want to pull only the earliest date from a table that has two date fields. It is possible that both date fields are null. However, I only want to pull values if at least one of the fields is not null. Also, if the first date field is not null, then I want that value. In other words, I prefer the first date field.

Here is what I've come up with, but I don't feel 100% this is correct. And, I'm also thinking there's a better way to skin the proverbial cat.

Select
o.order_num,
Case
    when ro.pay1_date is not null then ro.pay1_date 
    when ro.pay2_date is not null then ro.pay2_date
End as funding_date
From orders o
left join recon_order ro on(ro.order_id = o.order_id)
Where (ro.pay1_date is not null or ro.pay2_date is not null) 
Order by funding_date desc
Limit 500

I first ran this query without the Where clause, but that just returned 500 Null values, which I never completely understood.

And as a side note I'm working with an older version of PostgreSQL 8.1 (not my choice). But just saying this because perhaps the old version won't support certain new query syntax.

Thanks in advance for any input that corrects or improves this!

Vamsi Prabhala · Accepted Answer · 2017-01-19 02:37:28Z

1

You can use a least to get the least of the 2 dates. When both of them are null, it returns null. You can filter the null rows later.

SELECT *
FROM
  (SELECT o.order_num,
          least(ro.pay1_date,ro.pay2_date) as funding_date
   FROM orders o
   LEFT JOIN recon_order ro ON ro.order_id = o.order_id) x
WHERE funding_date IS NOT NULL

edited Jan 19, 2017 at 2:37

answered Jan 19, 2017 at 2:18

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

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

3 Comments

Robert Over a year ago

Cool, this works. It also pulls the same results as my query. But, is your version more efficient? Is there something you would say that makes it inherently better?

Vamsi Prabhala Over a year ago

the improvement over your query is that .. in case both the dates are non-null, your query outputs date1 (even when it is greater than date2)..because that is the first condition in the case expression and if satisfied the second condition won't be checked.

Robert Over a year ago

I think date 1, in this instance, will always be least, if it's not null, but better to be safe than sorry, so thanks again.

Collectives™ on Stack Overflow

PostgreSQL case statement for mutliple date fields

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related