DROP TABLE #TEMP1
SELECT ROW_NUMBER() OVER (PARTITION BY NAME_AND_ADDRESS ORDER BY DATE_5 DESC) AS Seq,E.DATE_5, E.POLICY_NUMBER, E.NAME_AND_ADDRESS,
CASE WHEN E.SHORT_CODE_1 = 3 THEN '' ELSE M.EXP_PREM END AS PREMIUM 

INTO #TEMP1
FROM ENDORSEMENT E
INNER JOIN POLICY P ON E.POLICY_NUMBER = P.POLICY_NUMBER AND E.POLICY_DATE_TIME = P.POLICY_DATE_TIME
INNER JOIN MPL_EXPOSURE M ON E.POLICY_NUMBER = M.POLICY_NUMBER AND E.NAME_AND_ADDRESS = M.CLIENT_NUMBER AND E.POLICY_DATE_TIME =M.POLICY_DATE_TIME 
WHERE E.IDENTIFIER = '01Y' 
AND E.POLICY_NUMBER NOT LIKE 'Q%'
AND E.DATE_5 BETWEEN '1/1/12' AND '7/31/12' 
AND M.RATING_COUNTY NOT LIKE 'X%'
----AND E.DATE_5 = (SELECT MAX(DATE_5) FROM ENDORSEMENT E2 WHERE E.POLICY_NUMBER = E2.POLICY_NUMBER AND E.NAME_AND_ADDRESS = E2.NAME_AND_ADDRESS)
AND E.DATE_5 = (SELECT MAX(DATE_5)FROM ENDORSEMENT E2 WHERE E.POLICY_NUMBER = E2.POLICY_NUMBER AND E.POLICY_DATE_TIME = E2.POLICY_DATE_TIME) 

SELECT * FROM #TEMP1 WHERE Seq=1 ORDER BY NAME_AND_ADDRESS, POLICY_NUMBER