Need query help - getting latest date

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

General SQL Server Forums

New to SQL Server Programming

Need query help - getting latest date

Author

Topic

sumdumgai
Starting Member

5 Posts

Posted - 2010-09-27 : 11:19:26

I need to pull only the records with the latest date into Excel from Access. As a simplified example, let's say the Access database has two tables:

Table T1 is price history with fields PartNum, PriceDate, Price. For each PartNum, there are multiple dates (PriceDate) and associated prices (Price).
example:
P1, 1/1/10, $1.00
P1, 2/1/10, $2.00
P1, 3/1/10, $3.00
P2, 1/1/10, $4.00
P2, 2/1/10, $3.00
P3, 1/1/10, $1.00

Table T2 has cost data and has fields PartNum, Cost.
example:
P1, $0.50
P2, $0.75

Using a query executed from within Excel, I need to pull into Excel from Access all PartNum's where the PartNum is in both tables, the latest PriceDate and Price for each PartNum, and the Cost for each PartNum.
results example:
P1, 3/1/10, $3.00, $0.50
P2, 1/1/10, $4.00, $0.75

I've tried this but it doesn't work.

Select T1.PartNum, max(T1.PriceDate), T1.Price, T2.PartNum, T2.Cost
From T1, T2
Where T1.PartNum = T2.PartNum

I'd appreciate any help here.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-09-27 : 11:52:56

Unless I'm not following what you're asking for, your sample output doesn't match with that you're asking for. But I think this is what you need ( if you're on SQL 2005 or later)


DECLARE @T1 table(PartNum char(2),Pricedate datetime,Price money)
DECLARE @t2 table(PartNum char(2),Cost money)


INSERT INTO @t1
SELECT 'P1', '1/1/10', $1.00  UNION ALL
SELECT 'P1', '2/1/10', $2.00  UNION ALL
SELECT 'P1', '3/1/10', $3.00  UNION ALL
SELECT 'P2', '1/1/10', $4.00  UNION ALL
SELECT 'P2', '2/1/10', $3.00  UNION ALL
SELECT 'P3', '1/1/10', $1.00
 

INSERT INTO @t2
SELECT 'P1', $0.50  UNION 
SELECT 'P2', $0.75

 
SELECT T1.PartNum,  T1.PriceDate , T1.Price, T2.PartNum, T2.Cost
From 
  (select PartNum, PriceDate ,Price
          ,rank() over(partition by  partnum order by pricedate desc) as Rank
   from @t1  
  ) T1
INNER JOIN @t2 T2
 ON T1.PartNum = T2.PartNum
WHERE t1.rank = 1

Jim

Everyday I learn something that somebody else already knew

sumdumgai
Starting Member

5 Posts

Posted - 2010-09-27 : 12:07:56

Thanks for your quick reply. You're right, I'm sorry. The results should be:
P1, 3/1/10, $3.00, $0.50
P2, 2/1/10, $3.00, $0.75

Please understand I know little about SQL. I'm working in Excel 2007 and want to use a Microsoft Query to import data from Access into a worksheet.

In my example, parts P1 and P2 exist in both tables. In table T1, the latest price date for P1 is 3/1/10 with a price of $3.00, and the latest price for P2 is 2/1/10 with a price of $3.00.

The cost for each is in table T2 and they are 0.50 and 0.75 respectively. Part P3 exists only in T1 so it should not br included in the results.

There are hundreds of records in each table.

Thanks.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-09-27 : 12:22:13

So my query works?

Jim

Everyday I learn something that somebody else already knew

sumdumgai
Starting Member

5 Posts

Posted - 2010-09-27 : 13:15:34

You're way ahead of me. I take it that the first part of your code simply builds test tables and the last part:

SELECT T1.PartNum, T1.PriceDate , T1.Price, T2.PartNum, T2.Cost
From
(select PartNum, PriceDate ,Price
,rank() over(partition by partnum order by pricedate desc) as Rank
from @t1
) T1
INNER JOIN @t2 T2
ON T1.PartNum = T2.PartNum
WHERE t1.rank = 1

actually pulls the data I want. I'll give it a try. Thanks.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-09-27 : 13:19:25

Yes, The @t1 and @t2 are table variables, which i used to test my query. This part makes use of the RANK() function available in sql 2005 and later
select PartNum, PriceDate ,Price
,rank() over(partition by partnum order by pricedate desc) as Rank
from @t1. YOu can run it separatley to see what it's doing

Good Luck!

Jim

Everyday I learn something that somebody else already knew

sumdumgai
Starting Member

5 Posts

Posted - 2010-09-27 : 15:01:22

Having problems with syntax I think. Here's my attempt at converting your statements:

SELECT `Price History`.`PART NUMBER`, `Price History`.`NET PRICE`, `Price History`.Date, `Part ID`.PNUM, `Part ID`.COST
From
(select `PART NUMBER`, `NET PRICE`, Date
,rank() over(partition by `PART NUMBER` order by Date desc) as Rank
from @`Price History`
) T1
INNER JOIN @`Part ID` T2
ON T1.`PART NUMBER` = T2.PNUM
WHERE T1.rank = 1

Getting error: "Could not add the table '(select'."

Thanks.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-09-27 : 15:20:13

Are you using Microsoft SQL Server 2005 or later? If so, does my example work when run in the Management Studio?

Jim

Everyday I learn something that somebody else already knew

sumdumgai
Starting Member

5 Posts

Posted - 2010-09-27 : 15:52:49

I'm using Excel 2007. Have never used Management Studio. When you import external data into Excel using Microsoft Query, the query wizard generates the SQL and ODBC commands for you . So I'm not using Microsoft SQL Server, at least that I know of.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-09-28 : 08:11:42

Sorry, I missed that you are using Access. You may be better off in an Access forum, like www.dbforums.com. You can actually make your own ranking function in Access by joining a table to itself in a query. I've done it before and if I dig that code up I'll post it here.

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources