View including date comparison

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 2000 Forums

SQL Server Development (2000)

View including date comparison

Author

Topic

sand-lakes1
Starting Member

29 Posts

Posted - 2002-07-12 : 05:05:40

Hi all,

We are using a table called pnrpiceshistory including partnumbers and prices. Prices can change and therefore the table includes all historical prices. Table is updated one a week. Example included:

pn price

100143-1 1.9344 USD Americas 7/11/2002
100143-1 1.9201 USD Americas 6/13/2002
100145-1 2.8184 USD Americas 6/13/2002
100147-1 2.4752 USD Americas 6/13/2002
100159-1 1.2896 USD Americas 6/13/2002
100161-1 1.4248 USD Americas 6/13/2002
100267-4 3.9104 USD Americas 6/13/2002

I want to create a view which only shows unique pn's together with the date of update. As you can see '100143-1' is double in it. I only want to see one '100143-1' with the price and date of 7/11/2002.
View should show something like this:

100143-1 1.9344 USD Americas 7/11/2002
100145-1 2.8184 USD Americas 6/13/2002
100147-1 2.4752 USD Americas 6/13/2002
100159-1 1.2896 USD Americas 6/13/2002
100161-1 1.4248 USD Americas 6/13/2002
100267-4 3.9104 USD Americas 6/13/2002

If the price of '100145-1' changes next week I want to see that record included and not the one which exists now.

Someone can help ?

Edited by - sand-lakes1 on 07/12/2002 05:09:59

rksingh024
Yak Posting Veteran

56 Posts

Posted - 2002-07-12 : 05:50:40

This should help you...

select pnr, col2, col3, updated_date
from table1 t1
where updated_date = (select max(updated_date) from table1 t2
where t2.pnr = t1.pnr)

Ramesh

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-07-12 : 06:05:24

you can use a derived table.....a "forum search" will turn up several useful items....

but a quick sample is below.....

select * from pnrpiceshistory a
join (select b.pn, max(enddate) as b_enddate from pnrpiceshistory b
group by b.pn) as b
on a.pn = b.pn and a.enddate = b.b_enddate

sand-lakes1
Starting Member

29 Posts

Posted - 2002-07-12 : 06:21:25

Thanks for the help problem solved.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources