|
|
Posted by TGEAR on 06/14/06 20:14
I have an ItemSTDPriceHistory table as below and this is a child table
of itemlookup table with one to many relationship.
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[ItemSTDPriceHistory]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[ItemSTDPriceHistory]
GO
CREATE TABLE [dbo].[ItemSTDPriceHistory] (
[index_id] [int] NOT NULL ,
[ItemLookUpID] [int] NOT NULL ,
[dtbegin] [datetime] NOT NULL ,
[price] [decimal](18, 0) NOT NULL ,
) ON [PRIMARY]
GO
I try to get the output of the most latest date for each ItemLookUpID
and
these are sample records. (I am sorry I do not know how to write a sql
statment with records in it)
index_id ItemLookupID dtbegin price
-------------------------------------------------------------------------------------------
1 4 4/2/2006 1500
2 4 4/8/2006 2000
3 3 4/1/2006 50
4 3 5/7/2006 80
5 2 8/4/2006 67
6 2 9/4/2006 55
7 2 9/6/2006 500
so the correct output will be as below (the latest date with each
itemlookupID)
index_id ItemLookupID dtbegin price
-------------------------------------------------------------------------------------------
1 2 9/6/2006 500
2 3 5/7/2006 80
3 4 4/8/2006 2000
I wrote the sql stmt as below, but it only fetched as below (the most
latest date among all records which is wrong).
index_id ItemLookupID dtbegin price
-------------------------------------------------------------------------------------------
1 4 5/7/2006 80
select i.*, h.dtbegin, h.price
from ItemLookUp i RIGHT OUTER JOIN ItemSTDPriceHistory h
ON i.index_id = h.ItemLookUpID
where h.dtbegin = (select max(h.dtbegin) from ItemLookUp i )
order by i.itemnsn, i.itemdescription, i.itemunit, h.dtbegin, h.price
asc
Navigation:
[Reply to this message]
|