Re: Record (entity) versions...

sztomi_at_seznam.cz (sz Tomi) wrote:

[snip]

>SELECT i.date,c.name,c.address FROM invoices AS i INNER JOIN customers
>AS c ON i.CustomerId=c.CustomerId
>GO
>
>date name address
>
>2004-01-01 00:00:00.000 First Company Ltd. Boston
>2004-01-02 00:00:00.000 First Company Ltd. Boston
>2004-01-02 00:00:00.000 Second Company Ltd. Washington
>2004-01-03 00:00:00.000 Second Company Ltd. Boston
>
>Is it possible in any of the RDBMS's to make this last query return
>the following result set?
>
>2004-01-01 00:00:00.000 First Company Ltd. New York
>2004-01-02 00:00:00.000 First Company Ltd. New York
>2004-01-02 00:00:00.000 Second Company Ltd. Washington
>2004-01-03 00:00:00.000 First Company Ltd. Boston

     No, because you do not have enough information. You need to associate a date range with the addresses and use that date range with the customer id to select the then-current address. To my somewhat less than expert eyes, this would involve creating a table of addresses keyed to customer id and effective date.

     To those more expert, am I on the right track?

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:

     I have preferences.
     You have biases.
     He/She has prejudices.