Help with Max Date - SQL Server Forums

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

Transact-SQL (2000)

Help with Max Date

Author

Topic

BigRedFish
Starting Member

4 Posts

Posted - 2011-01-13 : 21:59:38

I have 2 tables that are only linked to the date via a version ID with each product containing 1 or more versions. I need to find for each product the MAX date row only
Adding a image for better representation... basically I want 1 record from Table1 for each Product but has the latest Date from Table2 for that products versions.

Table1
ID PRODUCT CODE versionId
1- 13416- C2370 - 10404
2- 13416- C3640 - 1
3- 13417- C1232 -1
4- 13417- C1222 - 10404
5- 13418- C4124 - 25465
6- 13419- C2370 - 25463

Table2
version_id Date
1 1/1/1990
10404 11/6/2007
25463 10/22/2008
25465 10/22/2008

What I want
T1.ID T1.PRODUCT T1.CODE T2.Date
1- 13416 - C2370 - 11/6/2007
4- 13417 - C1222 - 11/6/2007
5- 13418 - C4124 - 10/22/2008
6- 13419 - C2370 - 10/22/2008

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-01-13 : 22:39:54

[code]
select T1.ID, T1.PRODUCT, T1.CODE, T2.Date
from Table1 T1
inner join Table2 T2 on T1.versionId = T2.versionId
where T2.Date = (
select max(x2.Date)
from Table1 x1
inner join Table2 x2 on x1.versionId = x2.versionId
where x1.PRODUCT = T1.PRODUCT
)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-01-14 : 01:16:24

Funny. Edited the post but no response to khtan's answer...so what?

No, you're never too old to Yak'n'Roll if you're too young to die.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-01-14 : 02:31:58

looks like OP only added the pic

KH
[spoiler]Time is always against us[/spoiler]

BigRedFish
Starting Member

4 Posts

Posted - 2011-01-14 : 12:07:58

I hadn't had a chance to try it but I did this morning and it works great for the sample data I provided... but I forgot a few things and when I tried to run it on mine it runs for more than 5 minutes and so I cancel the query... I did leave out a location column from Table1 that I want to filter out buy only 1 location...

Let me post the actual tables and see what you all can come up with maybe I need to select to a temp and then do max effective date agains that..
So what I need is the highlighted rows for only that one Carrier. (the table has 27000+ records for this one carrier and there are probably 2500+ carriers)
Please help

and Thank you :)

BigRedFish
Starting Member

4 Posts

Posted - 2011-01-14 : 12:20:45

Maybe I got it.... Can you guys take a look and see if this is decent code...

SELECT pv.product, pv.code, Max([pv.date])
FROM   (SELECT p.Product, 
	    p.carrier, 
	    p.code, 
	    [v.date]
	    FROM  CodesVersion v INNER JOIN PCodes p ON v.version_id = p.version_id
	    WHERE p.carrier = '11021') pv
Group By pv.product, pv.code
ORDER BY pv.product

BigRedFish
Starting Member

4 Posts

Posted - 2011-01-14 : 12:28:44

nope.. I got less but it still gives me duplicates for some of the products.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources