Wednesday, March 28, 2012

Max Dates For Cost Query

Hi,

I am trying to identify the costs for products with the latest cost date. I am unable to run the query. Is there something that I can do?

SELECT PART,COST, DATE FROM DATA/COSTFILE AS T1 WHERE T1.DATE= (SELECT MAX(DATE) AS T2 FROM DATA/COSTFILE AS T2 WHERE T2.PART=T1.PART)

Thanks,

DavidOriginally posted by hidvegi
Hi,

I am trying to identify the costs for products with the latest cost date. I am unable to run the query. Is there something that I can do?

SELECT PART,COST, DATE FROM DATA/COSTFILE AS T1 WHERE T1.DATE= (SELECT MAX(DATE) AS T2 FROM DATA/COSTFILE AS T2 WHERE T2.PART=T1.PART)

Thanks,

David

SELECT
T1.PART,
T1.COST,
T1.DATE
FROM
[DATA/COSTFILE] AS T1
WHERE
T1.DATE= (
SELECT MAX(T2.DATE) AS MAXDATE
FROM DATA/COSTFILE AS T2
WHERE T2.PART=T1.PART)

FYI, you shouldn't use ANY special characters in your column names or table names, especially "/\-_.,&%@.+"=".

The only exception to this is "_" which can be used after for sp_name on stored procedures that must reside in master and run on every database.

No comments:

Post a Comment