|
|
Posted by TGEAR on 06/14/06 21:22
the reply status shows 1 new of 2
i think someone replied for my posting, but cannot see it.
what happened?
TGEAR wrote:
> 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]
|