quote:

---

Originally posted by divan

Thanks Jimf that worked.... Now I am trying to figure out how to incooperate this in the following either in the INNER JOIN or in the WHERE in the following

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 ---AND EXP_PREM > 0
INNER JOIN CLIENT C ON E.NAME_AND_ADDRESS = C.CLIENT_NUMBER
INNER JOIN (SELECT M1.CLIENT_NUMBER, M1.EXP_PREM, MAX(M1.POLICY_DATE_TIME) AS Latest
FROM MPL_EXPOSURE M1
WHERE M1.EXP_PREM <> 0
GROUP BY M.CLIENT_NUMBER)cl1
ON cl1.CLIENT_NUMBER = M.CLIENT_NUMBER
AND cl1.Latest = M.POLICY_DATE_TIME
INNER JOIN EDIT_LONG_CODE E5 ON E5.TBNAME = 'MPL_EXPOSURE' AND E5.NAME = 'SPECIALTY' AND E5.CODE = E.LONG_CODE_1
INNER JOIN EDIT_SHORT_CODE E6 ON E6.TBNAME = 'MPL_EXPOSURE' AND E6.NAME = 'EXPOSURE_TYPE' AND E6.CODE = E.SHORT_CODE_1
INNER JOIN EDIT_LONG_CODE E7 ON E7.TBNAME = 'MPL_EXPOSURE' AND E7.NAME = 'RATING_COUNTY' AND E7.CODE = M.RATING_COUNTY
INNER JOIN EDIT_LONG_CODE E8 ON E8.TBNAME = 'MPL_EXPOSURE' AND E8.NAME = 'LIAB_LMT' AND E8.CODE = E.LONG_CODE_2
--INNER JOIN MPL_EXPOSURE M2 ON E.POLICY_NUMBER = M2.POLICY_NUMBER AND P.POLICY_DATE_TIME = M2.POLICY_DATE_TIME

WHERE E.IDENTIFIER = '01Y'
AND E.DATE_5 BETWEEN '1/1/12' AND '7/31/12'
----AND E.POLICY_DATE_TIME = (SELECT MAX(POLICY_DATE_TIME)FROM ENDORSEMENT E2 WHERE E.POLICY_NUMBER = E2.POLICY_NUMBER)
AND E.DATE_5 = (SELECT MAX(DATE_5)FROM ENDORSEMENT E4 WHERE E.POLICY_NUMBER = E4.POLICY_NUMBER)
AND E.POLICY_NUMBER NOT LIKE 'Q%'
AND M.RATING_COUNTY NOT LIKE 'X%'
---AND E.NAME_AND_ADDRESS = 1133

---