|
divan
Posting Yak Master
121 Posts |
 Posted - 08/28/2012 : 16:18:03
|
Well I do not expect you to do my homework and I do not post unless I have tried everything I can think of.. but just in case you still do not believe me here is the script I wrote
SELECT DISTINCT E.POLICY_NUMBER,
P.POL_EFF_DATE,
P.POL_EXP_DATE,
E.NAME_AND_ADDRESS AS CLIENT_NUMBER,
E5.DESCRIPTION AS SPECIALTY,
C.FNAME1 AS FIRST_NAME,
C.LNAME1 AS LAST_NAME,
E6.DESCRIPTION AS EXPOSURE_TYPE,
E.DATE_1 AS RETRO_DATE,
E7.DESCRIPTION AS RATING_COUNTY,
E8.DESCRIPTION AS LIAB_LMT,
E.DATE_5 AS TERMINATION_DATE,
CASE WHEN M.EXPOSURE_TYPE = 3 THEN P.ANNUAL_PREMIUM ELSE M.EXP_PREM END AS PREMIUM,
CASE WHEN P.POLICY_NUMBER LIKE 'T%' OR P.POLICY_NUMBER LIKE 'C%' THEN 'Texas' ELSE 'Mississippi' END AS ISSUED_STATE, P.POL_TERM_DATE
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 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
the issue is that the last record in the MPL_EXPOSURE table the EXP_PREM field is zero only because the exposure was deleted using the ENDORSEMENT table.. So what I am looking for is a way to find the record just before the last record in the MPL_EXPOSURE that has the EXP_PREM with a dollar amount... |
 |
|
Select
