|
|
Posted by TGEAR on 06/14/06 20:44
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
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 ItemSTDPriceHistory
h)
order by i.itemnsn, i.itemdescription, i.itemunit, h.dtbegin, h.price
asc
so the correct output will be as below (the latest date with each
itemlookupID) and please help with my sql stmt to output the records
as below
index_id ItemLookupID dtbegin price
-------------------------------------------------------------------------------------------
1 2 9/6/2006 500
2 3 5/7/2006 80
3 4 4/8/2006 2000
Navigation:
[Reply to this message]
|