Bill,
I would think something like this should work:
select I_CONTROL#, I_STATUS, I_TMSP, O_OWNER
from inventory join owner on i_control# = o_control#
where O_TMSP <= I_TMSP
-Jeff
<bill.blalock@xxxxxxxx> 09/14/07 5:02 PM >>>
I am trying to do this problem with SQL instead of logicals. There are
the key fields of two tables.
INVENTORY A table
I_CONTROL#
I_STATUS (really a lot of informatoin)
I_TMSP
OWNER B table
O_CONTROL#
O_OWNER
O_TMSP
The status of an item is in the INVENTORY file, the owner of the item is
in the OWNER file. The owner of an item (control#) is independent of its
status.
There is always one owner of each item. During the course of time the
owner of an item in inventory may change.
The status is what happens to the item in inventory over time.
Each items is created with an initial ower and inventory record.
Looking back in time the owner of an item in inventory is
B.CONTROL# = A.CONTROL# AND B.TMSP <= A.TMSP
For a given INVENTORY record using logical files I would
SETGT(I_CONTROL, I_TMSP) OWNER
READPE(I_CONTROL) OWNER
How would an SQL query be constructed to give a result of
I_CONTROL#, I_STATUS, I_TMSP, O_OWNER
Given that the O_OWNER is the record with O_TMSP equal to or earlier than
the I_TMSP
AN example
I_CONTROL# = 1
I_STATUS = purchased the car
I_TMSP = 2/1/06@4:00
O_CONTROL# = 1
O_OWNER = Bill
O_TMSP = 2/1/06@4:00
I_CONTROL# = 1
I_STATUS = drove to Tuscaloosa
I_TMSP = 2/2/06@3:00
I_CONTROL# = 1
I_STATUS = drove to birmingham
I_TMSP = 2/3/06@4:00
I_CONTROL# = 1
I_STATUS = washed car
I_TMSP = 2/4/06@10:00
I_CONTROL# = 1
I_STATUS = traded in car
I_TMSP = 2/5/06@4:00
O_CONTROL# = 1
O_OWNER = Charlie
O_TMSP = 2/6/06@11:00
I_CONTROL# = 1
I_STATUS = drove car off lot
I_TMSP = 2/6/06@11:10
I_CONTROL# = 1
I_STATUS = drove car to memphis
I_TMSP = 2/7/06@12:00
CONTROL STATUS I_TIMESTAMP OWNER
1 purchased the car 2/1/06@4:00 Bill
1 drove to Tuscaloosa 2/2/06@3:00 Bill
1 drove to birmingham 2/3/06@4:00 Bill
1 washed car 2/4/06@10:00 Bill
1 traded in car 2/5/06@4:00 Bill
1 drove car off lot 2/6/06@11:10 Charlie
1 drove car to memphis 2/7/06@12:00 Charlie
Any thoughts on how to write a QUERY to produce this result set? On the
join I want only one record from OWNER, the record where CONTROL# are
equal and whose timestamp is equal or earlier to the one from the
INVENTORY record.
Thanks!
------------------
The information contained in this message is proprietary and/or confidential. If you are not the intended recipient, please: (i) delete the message and all copies; (ii) do not disclose, distribute or use the message in any manner; and (iii) notify the sender immediately. In addition, please be aware that any message addressed to our domain is subject to archiving and review by persons other than the intended recipient. Thank you.
------------------
midrange.com
