|
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 |
|
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' ) )
);
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
|
