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

On 9/15/07, bill.blalock@xxxxxxxx <bill.blalock@xxxxxxxx> wrote:

Steve

Maybe I am wrong here but I think somthing was left out.

yikes, I see what you mean! I still think you can do it with views. here is
how:

-- renaming the inventory table to what I understand it to be.
create view ItemEvents as
(
select i_control# itemId,
i_status eventText,
i_tmsp eventTs
from inventory
) ;

-- same with the owner table
create view ItemOwner as
(
select o_control# itemId,
o_owner ownerName,
o_tmsp OwnedAsOfTs
from owner
) ;

-- an intermediate view. used only by the next view.
-- ( not sure if a common table expression can be used within a view. )
-- Produces multiple rows for each event.
-- for each event, you get a row for each owner of the item at
-- and prior to the time of the event.
create view ItemEventsWithOwnerKey_Intermediate as
(
select a.itemid,
a.eventTs,
b.OwnedAsOfTs
from ItemEvents a
join ItemOwner b
on a.itemId = b.itemid
and a.eventTs <= b.OwnedAsOfTs
) ;

-- gives us the item events, with the key ( the ownerTs ) to
-- the owner at the time of the event.
create view ItemEventsWithOwnerKey as
(
select a.itemId, a.eventTs,
max(a.OwnedAsOfTs)
from ItemEventsWithOwnerKey_Intermediate a
group by a.itemId, a.eventTs
) ;

-- grand finale. report the item events, from first to last, with the
-- owner at the time of each event.
select a.itemId, a.eventTs, c.eventText, b.OwnerName
from ItemEventsWithOwnerKey a
join ItemOwner b
on a.itemId = b.itemId
and a.OwnedAsOfTs = b.OwnedAsOfTs
join ItemEvents c
on a.itemId = c.itemId
and a.eventTs = c.eventTs
order by a.itemId, a.eventTs ;