Insurance Carriers with Active Patients

In order to clean up my list of insurance carriers in SOS I would like to list all active Insurance Carrier’s/ Plans (defined as insurances with Patients tied to them) with their Address, Phone number, and NEIC Numbers. Actually I would like this list with just the count of active patient accounts, and a second list that includes the patients linked to each carrier.

This request is a little vague in that it is not clear whether the linked patients should be restricted to active patients or not. The first query assumes that it is only carriers linked to active patients (in the Active Patient List and without a discharge date), but you can just remove the flag and dischargedate conditions in the WHERE clause to get carriers linked to any patient.

SELECT
  c.payorname AS "Ins Plan",
  COUNT(a.ptnum) AS "ActivePatientCount",
  c.PayorNum,
  d.companynum AS "NEIC#",
  c.Addr1,
  c.Addr2,
  c.City,
  c.State,
  c.Zip,
  c.Phone1Desc,
  (c.phone1area+'-'+phone1) AS "Phone1",
  c.Phone2Desc,
  (c.phone2area+'-'+phone2) AS "Phone2",
  c.Phone3Desc,
  (c.phone3area+'-'+phone3) AS "Phone3"
FROM
  sos.patients a
  JOIN sos.ptpayors b ON a.ptnum = b.ptnum
  JOIN sos.payors c ON b.payornum = c.payornum
  JOIN sos.carriers d ON c.payornum = d.payornum
WHERE
  a.flag = 0
  AND a.dischargedate IS NULL
  AND c.payortype = 'I'
GROUP BY
  "Ins Plan",c.payornum,"NEIC#",c.Addr1, c.Addr2, c.City, c.State, c.Zip,c.Phone1Desc,"Phone1",c.Phone2Desc,"Phone2",c.Phone3Desc,"Phone3"
HAVING
  "ActivePatientCount" > 0
ORDER BY
  c.payorname, c.payornum

The second query removes the COUNT function, the GROUP BY and HAVING clauses, and adds the patient names and ID’s.

SELECT
  c.payorname AS "Ins Plan",
  c.PayorNum,
  (UPPER(a.lastname)+', '+a.firstname+' / '+a.id) AS "Patient Name/ID",
  d.companynum AS "NEIC#",
  c.Addr1 AS "InsAddr1",
  c.Addr2 AS "InsAddr2",
  c.City AS "InsCity",
  c.State AS "InsState",
  c.Zip AS "InsZip",
  c.Phone1Desc AS "InsPhone1Desc",
  (c.phone1area+'-'+phone1) AS "Phone1",
  c.Phone2Desc AS "InsPhone2Desc",
  (c.phone2area+'-'+phone2) AS "Phone2",
  c.Phone3Desc AS "InsPhone3Desc",
  (c.phone3area+'-'+phone3) AS "Phone3"
FROM
  sos.patients a
  JOIN sos.ptpayors b ON a.ptnum = b.ptnum
  JOIN sos.payors c ON b.payornum = c.payornum
  JOIN sos.carriers d ON c.payornum = d.payornum
WHERE
  a.flag = 0
  AND a.dischargedate IS NULL
  AND c.payortype = 'I'
ORDER BY
  c.payorname, c.payornum