Re: Need ideas for SQL join select to replace logical view

[Mark Adkins <adkinsm@xxxxxxxxxx>]

I don't think I saw a solution, so I thought I'd post.  The challenge is 
that you have a range that is defined by two records.  I assume the 
transfer of ownership takes at least a second.  ie. A person sells on one 
second and another person buys on the next second.

Regards, 

Mark

SELECT A.O_CONTROL#, A.O_OWNER, B.I_STATUS, B.I_TMSP

FROM (SELECT A.O_CONTROL#, A.O_OWNER, MIN(A.O_TMSP) AS OWNERSTART,
             MIN(IFNULL(B.O_TMSP,
                        TIMESTAMP('30000101000000')) - 1 SECOND)
              AS OWNEREND
      FROM OWNER AS A
       LEFT OUTER JOIN OWNER AS B
        ON A.O_CONTROL# = B.CONTROL# AND A.O_TMSP < B.O_TMSP
      GROUP BY A.O_CONTROL#, A.O_OWNER) AS A

 INNER JOIN INVENTORY AS B

  ON A.O_CONTROL# = B.I_CONTROL# AND
     B.I_TMSP BETWEEN A.OWNERSTART AND A.OWNEREND

ORDER BY 1,2,4

> message: 6
> date: Fri, 14 Sep 2007 16:02:59 -0500
> from: bill.blalock@xxxxxxxx
> subject: Need ideas for SQL join select to replace logical view
>
> 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!