March 20, 2003 at 9:38 am
Hi,
I want to create a view that contains the most recent status of a shipment. The view would be created from a table such as the following:
Shipment# Status Date
77650 shipped 12/23/02 14:10:00
77650 on site 12/23/02 14:00:00
77650 scheduled 12/02/02 07:30:00
77649 scheduled 12/01/02 07:34:00
77649 changed 12/06/02 08:45:00
77648 canceled 12/05/02 09:18:00
77648 scheduled 11/03/02 13:45:00
Based on the table data above I would like the view to contain the following:
Shipment# Status Date
77650 shipped 12/23/02 14:10:00
77649 changed 12/06/02 8:45:00
77648 canceled 12/05/02 09:18:00
Currently I'm using a stored procedure which uses cursors and stores each most recent status in a temp table. I'd like to just have a View contain the most recent status of each shipment. Is this possible?
March 20, 2003 at 11:14 am
create view mostrecent
as
select shipmentid, status, date
from shipments a
inner join (
select shipmentid, max(date) as maxdate from shipments
group by shipmentid
) b
on a.shipmentid = b.shipmentid
and a.date = b.maxdate
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
March 20, 2003 at 5:38 pm
Worked great!!! thanks... You expert DBA's on this site are a life saver.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply