Re: Record (entity) versions...
From: Jan Hidders <jan.hidders_at_REMOVETHIS.pandora.be>
Date: Sat, 10 Jul 2004 06:22:04 GMT
Message-ID: <gWLHc.181130$8U.8773678_at_phobos.telenet-ops.be>
>>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
Date: Sat, 10 Jul 2004 06:22:04 GMT
Message-ID: <gWLHc.181130$8U.8773678_at_phobos.telenet-ops.be>
Gene Wirchenko wrote:
> 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?
Since none of the real experts answered let me confirm that you are indeed on the right track. :-)
- Jan Hidders