Suggest a feature
×

Welcome to TagMyCode

Please login or create account to add a snippet.
0
0
 
0
Language: SQL
Posted by: Jenya Mia
Added: Jun 22, 2020 7:13 PM
Modified: Jun 22, 2020 7:15 PM
Views: 124
Tags: sql
  1. WITH summary AS (
  2.     SELECT DISTINCT
  3.            p.pol_no
  4.          , p.pol_mod
  5.          , p.pol_symbol_cd
  6.          , s.submission_id
  7.          , p.pol_term_eff_dt
  8.          , p.pol_term_exp_dt
  9.          , s.trans_type_ref_id
  10.          , ROW_NUMBER() OVER (partition BY p.pol_no
  11.                                   ORDER BY p.pol_mod DESC) AS rk
  12.       FROM ramt.submission s
  13.       JOIN ramt.policy p
  14.         ON s.policy_id = p.policy_id
  15.      WHERE s.tenant_cd = 'ALT'
  16.        AND ((p.pol_symbol_cd = 'WC' AND ramt.find_ud_ref_id('SOURCE_SYS', 'ONCORE') = s.source_sys_ref_id)
  17.              OR (p.pol_symbol_cd = 'SPP' AND ramt.find_ud_ref_id('SOURCE_SYS', 'LEGACYUD') = s.source_sys_ref_id))
  18.        AND p.non_renewal_ind = 1
  19.        AND p.pol_no IS NOT NULL
  20.        AND ramt.find_ud_ref_value(s.trans_type_ref_id, 'ITEM_DESC_SHORT') IN ('Renewal', 'New Business'))
  21. SELECT s.pol_no
  22.      , s.pol_mod
  23.      , s.pol_symbol_cd
  24.      , s.submission_id
  25.      , s.pol_term_eff_dt
  26.      , s.pol_term_exp_dt
  27.      , ramt.find_ud_ref_value(s.trans_type_ref_id, 'ITEM_DESC_SHORT') TranType
  28.   FROM summary s
  29.  WHERE s.rk = 1
  30.  ORDER BY s.pol_no
  31.      , s.submission_id