Re: Best way to effective date??

In article <2p1t4e$pp0_at_uuneo.neosoft.com>, Chad A. Brockman <chadb_at_NeoSoft.com> wrote:
>We are having a major debate over the best way to do effective dating. If we
>just use <entity><effective date><effective dated info> it seems that the sql
>to get at these dates is really wierd. It would have to involve major sorting
>(unless the field was indexed which can pretty much be assumed). So to get
>the effective data for a date would require something like:
>
>SELECT max(effective_date)
> FROM mytable
> where entity_id = :entity_id_var and
> effective_date <= :date_to_get_effective_data_for;
>
>This seems awfull ineffecient. Anybody got any better ideas???
If you are recording data as they come, i.e. always add later dates, and is mostly interested in the latest you could consider adding an (indexed) flag to the most recent. Your update (insert of the most recent) could the clear the current flag and set it for the newly inserted.
Otherwise your select seems OK too me
>
> Thanks,
>
>Chad A. Brockman
>chadb_at_NeoSoft.com
>VertiComp, Inc.
>
>
Received on Fri Apr 22 1994 - 08:40:26 CEST