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

Average Payment by Service and Payer for Period

I would like to compare payment rates by service codes for our various payers. Specifically, I would like to see the number of services rendered, total payments, and average payments grouped and sorted by service code and payer.

This little query illustrates a bunch of techniques including:

  • IF expression used as a SELECT column
  • Aggregate functions COUNT and SUM
  • CAST a value to a different data type
  • ROUND function
  • GROUP BY ROLLUP( )
SELECT
  (IF pay.payortype = 'I' THEN pay.payorname ELSE 'Private Pay' END IF) AS "Payer",
  chg.srvcode AS "SrvCode",
  COUNT(DISTINCT chg.jnum) AS "SrvCount", 
  SUM(IF COALESCE(cre.credtype,'Adjustment')= 'Adjustment' THEN 0 ELSE cre.crsplamt END IF) AS "TotPaid",
  CAST(ROUND(("TotPaid"/"SrvCount"),2) AS DECIMAL(10,2)) AS "AvgPaid"
FROM
  sos.rv_charges chg
  JOIN sos.ptpayors ptp ON chg.ptpayornum = ptp.ptpayornum
  JOIN sos.payors pay ON ptp.payornum = pay.payornum
  LEFT OUTER JOIN sos.rv_creditsplits cre ON chg.chgsplnum = cre.chgsplnum
WHERE
  chg.trandate BETWEEN '2000-01-01' AND '2000-12-31'
  AND chg.amount > 0
GROUP BY
   ROLLUP("SrvCode","Payer")
ORDER BY
  "SrvCode","Payer"

Balance Breakdown By Patient On Specified Date

 Our auditors have requested a report of outstanding balances per client, as of Dec 31, 2011, separated by liability (Medicare, Medicaid, private insurance and self-pay). Insurance type is specified by “Coverage Type” on the Additional tab of the Insurance Carrie/Plan. We want to report patients in either the Active or Inactive lists, as long as they have a balance of some kind at the end of 2011.

Because we are looking for balances on a previous date, we have to tally up all the charges and payments on or before that date, then subtract the latter from the former to find the balance. To do that will require a bunch of embedded subqueries as you can see in the query below. If you want to run the query for a different date, be sure to replace ALL the instances of ‘2011-12-31’!

The output specified in this example will go to an HTML format file called AUDIT2011.HTML located in the C:\SOS folder. You can output the results to screen for inspection before outputting to file by inserting two dashes or slashes at the beginning of the last line. Just remove those characters when you are ready to generate the file. The benefit of outputting in HTML format is that you can view the results in a web browser, or open the file with Excel for additional massaging.

SELECT
   a.lastname, a.firstname, a.id AS "Account ID",
   -- Medicare
   COALESCE((SELECT SUM(chgsplamt) 
   FROM sos.jchgsplits chs JOIN sos.ptpayors ptp ON chs.ptpayornum = ptp.ptpayornum JOIN sos.carriers car ON ptp.payornum = car.payornum
   WHERE chs.ptnum = a.ptnum AND car.coverage = 'C' AND chs.chgspldate <= '2011-12-31'),0) AS "Medicare Charges",
   COALESCE((SELECT SUM(crsplamt) 
   FROM sos.jcrsplits crs 
     JOIN sos.jchgsplits chs ON crs.chgsplnum = chs.chgsplnum 
     JOIN sos.ptpayors ptp ON chs.ptpayornum = ptp.ptpayornum 
     JOIN sos.carriers car ON ptp.payornum = car.payornum 
     JOIN sos.jcredits cre ON crs.jnum = cre.jnum 
     JOIN sos.journal jou ON cre.jnum = jou.jnum
   WHERE chs.ptnum = a.ptnum 
     AND car.coverage = 'C' 
     AND chs.chgspldate <= '2011-12-31' 
     AND jou.trandate <= '2011-12-31') ,0) AS "Medicare Credits",
   ("Medicare Charges" - "Medicare Credits") AS "Medicare Balance",
  -- Medicaid
   COALESCE((SELECT SUM(chgsplamt) 
   FROM sos.jchgsplits chs JOIN sos.ptpayors ptp ON chs.ptpayornum = ptp.ptpayornum JOIN sos.carriers car ON ptp.payornum = car.payornum
   WHERE chs.ptnum = a.ptnum AND car.coverage = 'D' AND chs.chgspldate <= '2011-12-31'),0) AS "Medicaid Charges",
   COALESCE((SELECT SUM(crsplamt) 
   FROM sos.jcrsplits crs 
     JOIN sos.jchgsplits chs ON crs.chgsplnum = chs.chgsplnum 
     JOIN sos.ptpayors ptp ON chs.ptpayornum = ptp.ptpayornum 
     JOIN sos.carriers car ON ptp.payornum = car.payornum 
     JOIN sos.jcredits cre ON crs.jnum = cre.jnum 
     JOIN sos.journal jou ON cre.jnum = jou.jnum
   WHERE chs.ptnum = a.ptnum 
     AND car.coverage = 'D' 
     AND chs.chgspldate <= '2011-12-31' 
     AND jou.trandate <= '2011-12-31' ) ,0) AS "Medicaid Credits",
   ("Medicaid Charges" - "Medicaid Credits") AS "Medicaid Balance",
   -- Private Ins 
   COALESCE((SELECT SUM(chgsplamt) 
   FROM sos.jchgsplits chs JOIN sos.ptpayors ptp ON chs.ptpayornum = ptp.ptpayornum JOIN sos.carriers car ON ptp.payornum = car.payornum
   WHERE chs.ptnum = a.ptnum AND car.coverage NOT IN ('C','D') AND chs.chgspldate <= '2011-12-31'),0) AS "Private Ins Charges",
   COALESCE((SELECT SUM(crsplamt) 
   FROM sos.jcrsplits crs 
     JOIN sos.jchgsplits chs ON crs.chgsplnum = chs.chgsplnum 
     JOIN sos.ptpayors ptp ON chs.ptpayornum = ptp.ptpayornum 
     JOIN sos.carriers car ON ptp.payornum = car.payornum 
     JOIN sos.jcredits cre ON crs.jnum = cre.jnum 
     JOIN sos.journal jou ON cre.jnum = jou.jnum
   WHERE chs.ptnum = a.ptnum 
     AND car.coverage NOT IN ('C','D') 
     AND chs.chgspldate <= '2011-12-31' 
     AND jou.trandate <= '2011-12-31' ) ,0) AS "Private Ins Credits",
   ("Private Ins Charges" - "Private Ins Credits") AS "Private Ins Balance",
   -- Self-Pay
   COALESCE((SELECT SUM(chgsplamt) 
   FROM sos.jchgsplits chs JOIN sos.ptpayors ptp ON chs.ptpayornum = ptp.ptpayornum JOIN sos.payors pay ON ptp.payornum = pay.payornum
   WHERE chs.ptnum = a.ptnum AND pay.payortype IN ('P','O') AND chs.chgspldate <= '2011-12-31'),0) AS "Self-Pay Charges",
   COALESCE((SELECT SUM(crsplamt) 
   FROM sos.jcrsplits crs 
     JOIN sos.jchgsplits chs ON crs.chgsplnum = chs.chgsplnum 
     JOIN sos.ptpayors ptp ON chs.ptpayornum = ptp.ptpayornum 
     JOIN sos.payors pay ON ptp.payornum = pay.payornum
     JOIN sos.jcredits cre ON crs.jnum = cre.jnum 
     JOIN sos.journal jou ON cre.jnum = jou.jnum
   WHERE chs.ptnum = a.ptnum 
     AND pay.payortype IN ('P','O') 
     AND chs.chgspldate <= '2011-12-31' 
     AND jou.trandate <= '2011-12-31' ) ,0) AS "Self-Pay Credits",
   ("Self-Pay Charges" - "Self-Pay Credits") AS "Self-Pay Balance"

FROM 
  sos.patients a  
WHERE
  "Medicare Balance" <> 0
  OR "Medicaid Balance" <> 0
  OR "Private Ins Balance" <> 0
  OR "Self-Pay Balance" <> 0
ORDER BY
  a.lastname, a.firstname, a.id

 

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

 

Insurance Services by Carrier for Period

We looking for a report that will list all sessions that have been billed for each insurance company during a month. For example, the report would list out many sessions have been billed for MVP insurance for the month of October. Please let me know if this is possible.

In the following query, the output is directed to an HTML file that can be viewed in a web browser, or loaded in Excel for additional manipulation.

SELECT DISTINCT
  pay.payorname, 
  pt.lastname, pt.firstname, pt.id, 
  jou.trandate, srv.srvcode, prv.provcode, jou.amount
FROM 
  sos.journal jou 
  JOIN sos.jcharges chg ON jou.jnum = chg.jnum
  JOIN sos.jchgsplits chs ON chg.jnum = chs.jnum
  JOIN sos.ptpayors ptp ON chs.ptpayornum = ptp.ptpayornum
  JOIN sos.payors pay ON ptp.payornum = pay.payornum
  JOIN sos.patients pt ON jou.ptnum = pt.ptnum
  JOIN sos.services srv ON chg.servicenum = srv.servicenum
  JOIN sos.providers prv ON chg.providernum = prv.providernum
WHERE
  pay.payortype = 'I'
  AND trandate BETWEEN '2011-11-01' AND '2011-11-30'
ORDER BY
  pay.payorname, pt.lastname, pt.firstname, pt.id, jou.trandate ;
OUTPUT TO c:\sos\insforperiod.html FORMAT HTML