On Thu, May 12, 2011 15:51, David Johnston wrote:
>>
>> +Infinity was chosen as a default to avoid the complexities of
>> dealing with NULL logic in SELECTS. I suppose that the simplest
>> solution is to go with a date of 9999-12-31 and treat that value
>> like infinity.
>
> The "just make it work" solution has many merits - I would
> also probably just use 9999-12-31 as a close approximation
> for +infinity; which itself is just there because you are
> avoiding "estimate is unknown".
>
> Why bother updating the "expected_by" value once the conveyance
> is no longer pending? Do you not really care if something
> arrived early? Even if you do not currently it seems a waste
> to throw out the data when you can readily get the same result
> as-needed (CASE WHEN expected_by <= arrived_at THEN arrived_at
> ELSE expected_by END) without giving up the ability to calculate
The main reason to update expected_by is that sometimes the
conveyance arrives without the expected_by ever being set. Leaving
the expected_by value at infinity, or 99991231, or NULL, complicates
other parts of the system. However, leaving untouched expected_by
values that are less than the infinite value is doable and is a
better approach.
--
*** E-Mail is NOT a SECURE channel ***
James B. Byrne mailto:ByrneJB@Harte-Lyne.ca
Harte & Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3