SELECT E.RATING_COUNTY, E1.DESCRIPTION AS COUNTY, E.RATING_STATE,COUNT(*) AS RecordCount
FROM POLICY P
INNER JOIN MPL_DETAIL M ON P.POLICY_NUMBER = M.POLICY_NUMBER AND P.POLICY_DATE_TIME = M.POLICY_DATE_TIME
INNER JOIN REGISTER R ON P.POLICY_NUMBER = R.POLICY_NUMBER
AND P.POLICY_DATE_TIME = R.POLICY_DATE_TIME


INNER JOIN MPL_EXPOSURE E ON R.POLICY_NUMBER = E.POLICY_NUMBER
AND R.POLICY_DATE_TIME = E.POLICY_DATE_TIME
AND E.TERMINATION_DATE IS NULL
INNER JOIN EDIT_LONG_CODE E1 ON E1.TBNAME = 'MPL_EXPOSURE' AND E1.NAME = 'RATING_COUNTY' AND E1.CODE = E.RATING_COUNTY

WHERE R.POLICY_DATE_TIME = (SELECT MAX(POLICY_DATE_TIME)
FROM REGISTER R2
WHERE R.POLICY_NUMBER = R2.POLICY_NUMBER
AND R.PORTFOLIO_SET = R2.PORTFOLIO_SET
AND R2.CHECK_OUT IS NULL)
AND P.POL_EFF_DATE >= '9/1/11'
-- AND P.POL_EXP_DATE > '9/1/11'
AND ((E.EXPOSURE_TYPE = '1' AND E.TYPE_LMT <> '8')
OR (E.EXPOSURE_TYPE = '7')
OR (E.EXPOSURE_TYPE = '2' AND E.TYPE_LMT = '9'))
AND R.STATUS_1 NOT IN ('4','6','7')
AND LEFT(P.POLICY_NUMBER,1) <> 'Q'
GROUP BY E.RATING_COUNTY, E1.DESCRIPTION, E.RATING_STATE