Self Join + finding the Max dates of the data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Self Join + finding the Max dates of the data

Author

Topic

paulgrooby
Starting Member

1 Post

Posted - 2009-11-26 : 01:22:50

This one has me stumped completely = I have a table that looks like the following:
Id, AgreementNumber, ItemNumber,PurchasePrice, Validfrom

Sample data looks like this:
1 230000 AB123 15.25 1/01/2009
2 230000 AB123 17.5 15/02/2009
3 230000 AB123 18.56 10/08/2009
4 230000 PG1234 10.56 1/02/2009
5 230000 PG1234 11.56 5/03/2009
6 230001 AB123 10.56 10/01/2009
7 230001 AB123 15.65 10/02/2009
8 230001 AB123 14.23 4/03/2009
9 230000 ZP456 10.56 6/05/2009
10 230000 ZP456 15.26 7/06/2009
11 230000 ZP456 14.25 10/07/2009
12 230000 AB123 15.23 1/08/2009

What I'm trying to do is get the price at a point in time for the Agreement and Item, but also join it back so I get the previous price (assumption is that my dates and records always have enough data that I dont get a null for pervious date).

I can get the first part which is the max date per Agreement/Item

Select T1.AgreementNo,T1.Itemnumber,T1.Validfrom ,T1.Purchaseprice from tbl_PurchaseAgreements T1

where T1.ValidFrom = (Select MAX(ValidFrom) from tbl_purchaseagreements T2 where T1.Agreementno = t2.agreementno and t1.itemnumber = t2.Itemnumber)

produces :
AgreementNo Itemnumber Validfrom Purchaseprice
230001 AB123 2009-03-04 00:00:00.000 14.23000
230000 ZP456 2009-07-10 00:00:00.000 14.25000
230000 PG1234 2009-03-05 00:00:00.000 11.56000
230000 AB123 2009-08-10 00:00:00.000 18.56000

But how dow I get the previous price/valid from on the same line - got me stumped - Appreciate the help on the one.
P

kbhere
Yak Posting Veteran

58 Posts

Posted - 2009-11-26 : 01:32:02

Select T1.AgreementNo,T1.Itemnumber,T1.Validfrom ,T1.Purchaseprice from tbl_PurchaseAgreements T1
where T1.ValidFrom = (Select TOP 2 with ties ValidFrom from tbl_purchaseagreements T2 where T1.Agreementno = t2.agreementno and t1.itemnumber = t2.Itemnumber ORDER BY ValidFrom DESC)

Try This..

Balaji.K

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-30 : 04:54:44

quote:
Originally posted by kbhere

Select T1.AgreementNo,T1.Itemnumber,T1.Validfrom ,T1.Purchaseprice from tbl_PurchaseAgreements T1
where T1.ValidFrom = (Select TOP 2 with ties ValidFrom from tbl_purchaseagreements T2 where T1.Agreementno = t2.agreementno and t1.itemnumber = t2.Itemnumber ORDER BY ValidFrom DESC)

Try This..

Balaji.K

It should be

Select T1.AgreementNo,T1.Itemnumber,T1.Validfrom ,T1.Purchaseprice from tbl_PurchaseAgreements T1
where T1.ValidFrom in (Select TOP 2 with ties ValidFrom from tbl_purchaseagreements T2 where T1.Agreementno = t2.agreementno and t1.itemnumber = t2.Itemnumber ORDER BY ValidFrom DESC)

Becuase subquery returns two values and you can't use = for that

Madhivanan

Failing to plan is Planning to fail

kbhere
Yak Posting Veteran

58 Posts

Posted - 2009-12-01 : 03:35:32

quote:

It should be

Select T1.AgreementNo,T1.Itemnumber,T1.Validfrom ,T1.Purchaseprice from tbl_PurchaseAgreements T1
where T1.ValidFrom in (Select TOP 2 with ties ValidFrom from tbl_purchaseagreements T2 where T1.Agreementno = t2.agreementno and t1.itemnumber = t2.Itemnumber ORDER BY ValidFrom DESC)

Becuase subquery returns two values and you can't use = for that

Madhivanan

Failing to plan is Planning to fail

Ya.. That is right.. I have missed out that unknowingly..
Thanks for your correction..

K.Balaji

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-01 : 07:25:09

quote:
Originally posted by kbhere

quote:

It should be

Select T1.AgreementNo,T1.Itemnumber,T1.Validfrom ,T1.Purchaseprice from tbl_PurchaseAgreements T1
where T1.ValidFrom in (Select TOP 2 with ties ValidFrom from tbl_purchaseagreements T2 where T1.Agreementno = t2.agreementno and t1.itemnumber = t2.Itemnumber ORDER BY ValidFrom DESC)

Becuase subquery returns two values and you can't use = for that

Madhivanan

Failing to plan is Planning to fail

Ya.. That is right.. I have missed out that unknowingly..
Thanks for your correction..

K.Balaji

No problem

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources