Answers to: Is there a more elegant way to obtain the least of 3 dates?http://sqlanywhere-forum.sap.com/questions/20530/is-there-a-more-elegant-way-to-obtain-the-least-of-3-dates<p>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.</p>
<p>It all works fine, but surely there must be a simple, more elegant way to accomplish this??</p>
<div class="codehilite"><pre> <span class="n">SELECT</span> <span class="n">IF</span> <span class="n">empstat_date_start</span> <span class="n">IS</span> <span class="n">NOT</span> <span class="n">NULL</span> <span class="n">THEN</span> <span class="n">IF</span> <span class="n">empstat_date_start</span> <span class="o">></span> <span class="n">empworkauth_date_start</span> <span class="n">THEN</span> <span class="n">empstat_date_start</span> <span class="n">ELSE</span> <span class="n">empworkauth_date_start</span> <span class="n">ENDIF</span> <span class="n">ELSE</span> <span class="n">empworkauth_date_start</span> <span class="n">ENDIF</span> <span class="n">AS</span> <span class="n">wa_date_start</span><span class="p">,</span>
<span class="n">COALESCE</span><span class="p">(</span><span class="n">empstat_date_end</span><span class="p">,</span> <span class="n">ad_accrual_end_date</span><span class="p">)</span> <span class="n">AS</span> <span class="n">wa_empstat_date_end</span><span class="p">,</span>
<span class="n">COALESCE</span><span class="p">(</span><span class="n">emptype_date_end</span><span class="p">,</span> <span class="n">ad_accrual_end_date</span><span class="p">)</span> <span class="n">AS</span> <span class="n">wa_emptype_date_end</span><span class="p">,</span>
<span class="n">COALESCE</span><span class="p">(</span><span class="n">empworkauth_date_end</span><span class="p">,</span> <span class="n">ad_accrual_end_date</span><span class="p">)</span> <span class="n">AS</span> <span class="n">wa_empworkauth_date_end</span><span class="p">,</span>
<span class="n">IF</span> <span class="n">wa_empstat_date_end</span> <span class="o"><</span> <span class="n">wa_emptype_date_end</span> <span class="n">THEN</span> <span class="n">wa_empstat_date_end</span> <span class="n">ELSE</span> <span class="n">wa_emptype_date_end</span> <span class="n">ENDIF</span> <span class="n">AS</span> <span class="n">date_compare1</span><span class="p">,</span>
<span class="n">IF</span> <span class="n">date_compare1</span> <span class="o"><</span> <span class="n">wa_empworkauth_date_end</span> <span class="n">THEN</span> <span class="n">date_compare1</span> <span class="n">ELSE</span> <span class="n">wa_empworkauth_date_end</span> <span class="n">ENDIF</span> <span class="n">AS</span> <span class="n">wa_date_end</span><span class="p">,</span>
</pre></div>
<p>Thanks for any suggestions.
Bill</p>enWed, 05 Mar 2014 14:02:38 -0500Comment by Bill Aumen on Breck Carter's answerhttp://sqlanywhere-forum.sap.com/questions/20530/is-there-a-more-elegant-way-to-obtain-the-least-of-3-dates#20586<p>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.</p>Bill AumenWed, 05 Mar 2014 14:02:38 -0500http://sqlanywhere-forum.sap.com/questions/20530/is-there-a-more-elegant-way-to-obtain-the-least-of-3-dates#20586Comment by Breck Carter on Bill Aumen's questionhttp://sqlanywhere-forum.sap.com/questions/20530/is-there-a-more-elegant-way-to-obtain-the-least-of-3-dates#20549<p>Doncha wish there was a LEAST ( expression, expression [, ... ] )?</p>Breck CarterTue, 04 Mar 2014 14:49:51 -0500http://sqlanywhere-forum.sap.com/questions/20530/is-there-a-more-elegant-way-to-obtain-the-least-of-3-dates#20549Comment by Bill Aumen on Bill Aumen's questionhttp://sqlanywhere-forum.sap.com/questions/20530/is-there-a-more-elegant-way-to-obtain-the-least-of-3-dates#20546<p>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.</p>Bill AumenTue, 04 Mar 2014 12:02:25 -0500http://sqlanywhere-forum.sap.com/questions/20530/is-there-a-more-elegant-way-to-obtain-the-least-of-3-dates#20546Comment by Volker Barth on Bill Aumen's questionhttp://sqlanywhere-forum.sap.com/questions/20530/is-there-a-more-elegant-way-to-obtain-the-least-of-3-dates#20544<p>AFAIK, you will have to uncheck the current accepted answer by unckecking the according marker before you can accept the "correct one".</p>Volker BarthTue, 04 Mar 2014 10:32:15 -0500http://sqlanywhere-forum.sap.com/questions/20530/is-there-a-more-elegant-way-to-obtain-the-least-of-3-dates#20544Comment by Bill Aumen on Bill Aumen's questionhttp://sqlanywhere-forum.sap.com/questions/20530/is-there-a-more-elegant-way-to-obtain-the-least-of-3-dates#20543<p>I was going thru the same struggle trying to figure out how to make MIN() work.</p>Bill AumenTue, 04 Mar 2014 10:27:17 -0500http://sqlanywhere-forum.sap.com/questions/20530/is-there-a-more-elegant-way-to-obtain-the-least-of-3-dates#20543Comment by Bill Aumen on Bill Aumen's questionhttp://sqlanywhere-forum.sap.com/questions/20530/is-there-a-more-elegant-way-to-obtain-the-least-of-3-dates#20542<p>yes, it should. But I can't figure out how to remove the incorrect "accepted Answer" and change it to the correct one.</p>Bill AumenTue, 04 Mar 2014 10:25:02 -0500http://sqlanywhere-forum.sap.com/questions/20530/is-there-a-more-elegant-way-to-obtain-the-least-of-3-dates#20542Comment by tzup on Bill Aumen's questionhttp://sqlanywhere-forum.sap.com/questions/20530/is-there-a-more-elegant-way-to-obtain-the-least-of-3-dates#20538<p>Soooo, Breck's answer should be marked as the answer to this question, right?</p>tzupTue, 04 Mar 2014 06:06:39 -0500http://sqlanywhere-forum.sap.com/questions/20530/is-there-a-more-elegant-way-to-obtain-the-least-of-3-dates#20538Comment by Volker Barth on Bill Aumen's questionhttp://sqlanywhere-forum.sap.com/questions/20530/is-there-a-more-elegant-way-to-obtain-the-least-of-3-dates#20535<p>Just to link to a somewhat related question:)</p>
<ul>
<li><a href="http://sqlanywhere-forum.sap.com/questions/16008">How to use MAX() as a scalar function?</a></li>
</ul>Volker BarthTue, 04 Mar 2014 03:09:51 -0500http://sqlanywhere-forum.sap.com/questions/20530/is-there-a-more-elegant-way-to-obtain-the-least-of-3-dates#20535Comment by Bill Aumen on Breck Carter's answerhttp://sqlanywhere-forum.sap.com/questions/20530/is-there-a-more-elegant-way-to-obtain-the-least-of-3-dates#20534<p>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!</p>Bill AumenMon, 03 Mar 2014 20:04:32 -0500http://sqlanywhere-forum.sap.com/questions/20530/is-there-a-more-elegant-way-to-obtain-the-least-of-3-dates#20534Answer by Breck Carterhttp://sqlanywhere-forum.sap.com/questions/20530/is-there-a-more-elegant-way-to-obtain-the-least-of-3-dates/20533<p>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():</p>
<pre>SELECT LESSER ( COALESCE ( dt1, '9999-12-31' ),
LESSER ( COALESCE ( dt2, '9999-12-31' ),
COALESCE ( dt3, '9999-12-31' ) )
);
</pre>Breck CarterMon, 03 Mar 2014 19:57:40 -0500http://sqlanywhere-forum.sap.com/questions/20530/is-there-a-more-elegant-way-to-obtain-the-least-of-3-dates/20533