February 12, 2010 at 12:33 pm
say you have a table as follows
itemCode Price effectiveDate
2 30 1/1/2009
3 35 1/5/2009
2 35 6/1/2009
how would you write a query so that the output would be as follows
itemCode Price effectiveDate
2 35 6/1/2009
3 35 1/5/2009
I want to output all codes with the most recent effectiveDate
the following will not work because of the different effective dates.
SELECT P1.itemCode, P1Price, P1.effectiveDate
FROM Price P1
WHERE P1.effectiveDate = (SELECT MAX(P2.effectiveDate) FROM Price P2 WHERE P2.itemCode = P1.itemCode)
am I missing something here?
February 12, 2010 at 3:55 pm
Hi,
Why do you say that your query will not work? seems to me that it produces the desired output.
take a look at the following article.
José Cruz
February 12, 2010 at 4:03 pm
You're correct I did some more research and apparently it should work but because of the *** backward database I am pulling this from it doesn't
I'm connecting to a Cache database (as a linked server) on SS2005
Any other ideas on how I might be able to do this?
February 12, 2010 at 5:08 pm
You are going to have to identify what you mean when you say it doesn't work. The query is fine written the way you have it, however - running against Cache you might be running into a data type conversion problem.
I would try using a temp table and then join to the temp table:
SELECT ItemCode
,MAX(effectiveDate) As MaxEffDate
INTO #effdt
FROM Price
GROUP BY ItemCode;
-- Verify the values being returned, do they have a time component?
SELECT *
FROM #effdt;
-- use sp_help on the object in tempdb to validate the data types being returned, is it a datetime or something else?
SELECT P1.itemCode, P1Price, P1.effectiveDate
FROM Price P1
JOIN #effdt dt ON dt.ItemCode = p1.ItemCode AND dt.MaxEffDate = p2.effectiveDate;
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply