List Secondary Insurance Policies by Patient

I need a list of patients who have secondary insurance, including the secondary policy information (payer name, subscriber number, and group number).

The following solution takes into consideration that we could have a mix of active and inactive policies, with inactive policies above active ones in the list. We therefore have to filter out the policies that are not active on the date of the query, and re-assign corrected position numbers to the remaining policies. We can filter out the inactive ones using a simple WHERE condition, but renumber the rest requires use of an advanced RANK expression, including an optional PARTITION parameter. All that is included as a subquery in an outer container query that functions as an ad-hoc view by assigning the subquery an Alias (in this case X). That ad-hoc view is linked to the other normal view (rv_policies) and the patient table in the FROM clause. A condition in the WHERE of the outer query filters out the primary policy, leaving only lower order coverage.

SELECT 
 b.ptnum, b.lastname,b.firstname,b.id, a.payorname,a.insgroup,x.CurrentInsPos
FROM 
  sos.rv_policies a 
  JOIN sos.patients b ON a.ptnum = b.ptnum 
  JOIN (SELECT a.ptnum, a.ptpolnum, RANK() OVER (PARTITION BY a.ptnum ORDER BY a.ptnum,a.inspos) AS "CurrentInsPos"
        FROM sos.ptpolicies a 
        WHERE today() BETWEEN COALESCE(a.active,'1900-01-01') AND COALESCE(a.inactive,'2100-12-31')) X ON a.ptpolnum = x.ptpolnum
WHERE
  b.flag = 0
  AND b.licnum = 101
  AND x.currentinspos > 1
ORDER BY 
  b.lastname, b.firstname, b.id, x.currentinspos

The query above excludes the primary insurance. The version below includes all active policies in the result set, but only for patients who have two or more currently active policies.

SELECT 
  b.ptnum, b.lastname,b.firstname,b.id, 
  RANK() OVER (PARTITION BY a.ptnum ORDER BY a.ptnum,a.inspos) AS "CurrentInsPos",
  a.payorname,a.insgroup
FROM 
  sos.rv_policies a 
  JOIN sos.patients b ON a.ptnum = b.ptnum 
  JOIN (SELECT ptnum,count(*) as "Active Policies"
        FROM sos.ptpolicies a 
        WHERE today() BETWEEN COALESCE(a.active,'1900-01-01') AND COALESCE(a.inactive,'2100-12-31')
        GROUP BY ptnum
        -- HAVING "Active Policies" > 1 
       ) AS X ON a.ptnum = x.ptnum
WHERE
  b.flag = 0
  AND b.licnum = 101
  AND x."Active Policies" > 1
ORDER BY
  b.lastname,b.firstname,b.id,CurrentInsPos