The forum will be down for maintenance over the weekend of August 18-20, 2017. The forum will be shut down on the evening (EDT) of Friday, August 18. Downtime is unknown but may be up to two days. The forum will be restarted as soon as maintenance is complete.

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.0k334673
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.5k433604876
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: 630 times

last updated: 05 Mar '14, 14:02