April 13, 2011 at 4:00 pm
I have a table called (TABLE_A) and TABLE_A has several columns such as CODE, TYPE, CLASS, EFFECTIVE_DATE, PRICE, CATEGORY, PRICE, PRICE * 1.25. Every CODE has multiple records. For example, I have CODE '800000' and 'A4221CCMC' and each have several entries. How would I grab a single row for '800000' and a single row for 'A4221CCMC' with the highest (most recent) effective date?
800000 PDDIRECT 2001-01-01 00:00:00.000LIST200.00000250.0000000
800000 PDDIRECT 2010-01-01 00:00:00.000LIST200.00000250.0000000
800000 PDDIRECT 2010-08-01 00:00:00.000LIST200.00000250.0000000
800000 PDDIRECT 2010-08-24 00:00:00.000LIST200.00000250.0000000
800000 PDDIRECT 2011-03-01 00:00:00.000LIST200.00000250.0000000
A4221CCMCPDPERDIEM 2009-01-01 00:00:00.000LIST22.00000 27.5000000
A4221CCMCPDPERDIEM 2009-06-06 00:00:00.000LIST22.00000 27.5000000
A4221CCMCPDPERDIEM 2010-01-01 00:00:00.000LIST22.00000 27.5000000
A4221CCMCPDPERDIEM 2010-08-01 00:00:00.000LIST22.00000 27.5000000
A4221CCMCPDPERDIEM 2010-08-24 00:00:00.000LIST22.00000 27.5000000
A4221CCMCPDPERDIEM 2011-03-01 00:00:00.000LIST22.00000 27.5000000
April 13, 2011 at 4:51 pm
You could either use a subquery to get the max effective date per code and join it back to the original table or you could use the ROW_NUMBER approach.
April 15, 2011 at 7:23 am
Seomething like This.
(Please note that I havn't tested this and it might need tweaking).
SELECT CODE, TYPE, CLASS, EFFECTIVE_DATE, PRICE, CATEGORY, PRICE, MARKED_UP_PRICE
FROM (
SELECT CODE, TYPE, CLASS, EFFECTIVE_DATE, PRICE, CATEGORY, PRICE, PRICE * 1.25 AS MARKED_UP_PRICE,
ROW_NUMBER() OVER(PARTITION BY CODE, TYPE, CLASS ORDER BY CODE, TYPE, CLASS, EFFECTIVE_DATE DESC)AS ROW_INSTANCE
FROM TABLE_A
) AS DERIVED_TABLE_A
WHERE DERIVED_TABLE_A.ROW_INSTANCE = 1
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply