0

[![enter image description here][1]][1] SELECT
POLICY_NO, FORMATTED_POLICY_NUMBER, POLICY_EFFECTIVE_DATE AS Effectivedt, POLICY_REWRITTEN_FROM Frompolicy, POLICY_EXPIRATION_DATE FromExpirationDate FROM TableName WHERE Load_End_Date = '99991231'

The policy num and formatted policy num needs to be joined as one column and must get data based on max (renewal_no).

Let's say the max renewal num is 9 so I must get a 9th renewed policy num as the current policy number. So the both policy_no and formatted_policy_number column must be a single column as Current policy number based on max renewal number.

Sam
  • 103
  • 1
  • 1
  • 10
  • 1
    Are you self joining to the same table? Sample data and expected results would help here... – S3S Mar 28 '19 at 18:57
  • [Here is a good place to start](https://dba.meta.stackexchange.com/questions/2976/help-me-write-this-query-in-sql/2977#2977) and then [MCVE Documentation](https://stackoverflow.com/help/mcve) – S3S Mar 28 '19 at 19:03
  • renewal is on same table @UnhandledExcepSean – Sam Mar 28 '19 at 19:16
  • well the sample is policy num column is TPT0123449 FORMATTED POLICY NUM is TPT0123449 00 RENEWAL is 0. Now lets say the policy is insured. so the policy no will be the same TPT0123449 but formatted policy num will be TPT0123499 01 and renewal NO will be 1. If its renewed again then the policy num will be the same and formatted will change to TPT0123449 02. and Renewal will be 3 – Sam Mar 29 '19 at 11:52

0 Answers0