Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
divan
Posting Yak Master
153 Posts |
 Posted - 2012-08-28 : 15:56:24
|
| I have a table called ENDORSEMENT and in this table there is a field called CLIENT_NUMBER field. I have another table called EXPOSURE that has a CLIENT_NUMBER also in addition it has an AMOUNT field also. I need a select statement where I can join the two tables and then select last record in the EXPOSURE table that has the AMOUNT greater than zero. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
divan
Posting Yak Master
153 Posts |
Posted - 2012-08-28 : 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 wroteSELECT 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_DATEFROM ENDORSEMENT EINNER JOIN POLICY P ON E.POLICY_NUMBER = P.POLICY_NUMBER AND E.POLICY_DATE_TIME = P.POLICY_DATE_TIMEINNER 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_NUMBERINNER JOIN EDIT_LONG_CODE E5 ON E5.TBNAME = 'MPL_EXPOSURE' AND E5.NAME = 'SPECIALTY' AND E5.CODE = E.LONG_CODE_1INNER JOIN EDIT_SHORT_CODE E6 ON E6.TBNAME = 'MPL_EXPOSURE' AND E6.NAME = 'EXPOSURE_TYPE' AND E6.CODE = E.SHORT_CODE_1INNER 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_TIMEWHERE 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 = 1133the 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... |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Select