I need the least of 3 dates, all of which may be null. So I first Coalesce the ending parameter date with each, then have a multi-column select so I can use the intermediate columns instead of a single line column expression that would be too long for my brain to comprehend.

It all works fine, but surely there must be a simple, more elegant way to accomplish this??

    SELECT  IF empstat_date_start IS NOT NULL THEN IF empstat_date_start > empworkauth_date_start THEN empstat_date_start ELSE empworkauth_date_start ENDIF ELSE empworkauth_date_start ENDIF AS wa_date_start,
            COALESCE(empstat_date_end, ad_accrual_end_date)     AS wa_empstat_date_end,
            COALESCE(emptype_date_end, ad_accrual_end_date)     AS wa_emptype_date_end,
            COALESCE(empworkauth_date_end, ad_accrual_end_date) AS wa_empworkauth_date_end,
            IF wa_empstat_date_end < wa_emptype_date_end THEN wa_empstat_date_end ELSE wa_emptype_date_end ENDIF AS date_compare1,
            IF date_compare1 < wa_empworkauth_date_end THEN date_compare1 ELSE wa_empworkauth_date_end ENDIF AS wa_date_end,

Thanks for any suggestions. Bill

asked 03 Mar '14, 13:29

Bill%20Aumen's gravatar image

Bill Aumen
2.1k334674
accept rate: 16%


It's not clear to me how the posted code relates to the question, so it's also not clear what should be done if any/all of the dates are null, but "the least of" requirement seems a natural for LESSER():

SELECT LESSER ( COALESCE ( dt1, '9999-12-31' ), 
                LESSER ( COALESCE ( dt2, '9999-12-31' ), 
                         COALESCE ( dt3, '9999-12-31' ) )
              );
permanent link

answered 03 Mar '14, 19:57

Breck%20Carter's gravatar image

Breck Carter
26.9k438609883
accept rate: 21%

1

LESSER. Yes, that's exactly what I was missing. That indeed will do it. It is so obvious now, but that function escaped me. Thanks!

(03 Mar '14, 20:04) Bill Aumen
Replies hidden

Just to link to a somewhat related question:)

(04 Mar '14, 03:09) Volker Barth

Soooo, Breck's answer should be marked as the answer to this question, right?

(04 Mar '14, 06:06) tzup

yes, it should. But I can't figure out how to remove the incorrect "accepted Answer" and change it to the correct one.

(04 Mar '14, 10:25) Bill Aumen

I was going thru the same struggle trying to figure out how to make MIN() work.

(04 Mar '14, 10:27) Bill Aumen

AFAIK, you will have to uncheck the current accepted answer by unckecking the according marker before you can accept the "correct one".

(04 Mar '14, 10:32) Volker Barth

It appeared I successfully cleared the Accepted mark, but even after refreshing the page I would get a message that the answer was already accepted when I tried checking the other. Now after closing the browser, visiting a few other web sites, and trying again it let me. The magic of technology.

(04 Mar '14, 12:02) Bill Aumen
2

Doncha wish there was a LEAST ( expression, expression [, ... ] )?

(04 Mar '14, 14:49) Breck Carter

Yep! That seemed natural and I tried it: but a few syntax errors finally led me to look at your post and the docs more carefully.

(05 Mar '14, 14:02) Bill Aumen
showing 2 of 9 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×51
×1

question asked: 03 Mar '14, 13:29

question was seen: 668 times

last updated: 05 Mar '14, 14:02