Category: Queries – Insurance

Count of Patients by Provider with and without Medicaid

Posted on | by SOS Blog | Leave a Comment on Count of Patients by Provider with and without Medicaid

I need to know by provider how many active patients they have and how many have Medicaid coverage.

This query shows the combined use a an IF expression that evaluates based on a subquery that returns a count. In this case, if there is any Medicaid coverage (carriers.coverage = ‘D’) then the expression returns “Yes”, otherwise (no ‘caid coverage) it returns “No”.

SELECT
(a.provfname + ' '+a.provlname) AS "Provider",
(IF (SELECT COUNT(*)
    FROM ptpayors s1 JOIN payors s2 ON s1.payornum = s2.payornum
       JOIN carriers s3 ON s2.payornum = s3.payornum
    WHERE s1.ptnum = a.ptnum AND s3.coverage = 'D') > 0
THEN 'YES'
ELSE 'NO'
END IF)
AS "Medicaid",
COUNT(DISTINCT a.ptnum) AS "Pt Count"
FROM
rv_charges a
WHERE
a.trandate BETWEEN '2008-01-01' AND '2008-12-31'
GROUP BY
"Provider", "Medicaid"
ORDER BY
"Provider", "Medicaid"

 

Count Of Patients By Insurance Plan

Posted on | by SOS Blog | Leave a Comment on Count Of Patients By Insurance Plan

The SOS report “Patients by Insurance Carrier” lists every active patient. Can that report be trimmed to just giving the carrier and the number of active patients within each carrier, perhaps also with a grand total of active patients? Knowing the exact amount of patients we serve is helpful when negotiating with carriers about rates, etc.

The following query gives a count by Plan and (primary) Provider. For the count across providers, remove line 3, line 9, and the comma and d.provcode from the GROUP BY and ORDER BY clauses. Better yet, look at the second version of the query below, featuring a ROLLUP that gives you all the totals and subtotals without the need to edit.

SELECT
  c.payorname AS "Ins Plan",
  d.provcode,
  count(a.ptnum) AS "N"
FROM
  sos.patients a
  JOIN sos.ptpayors b ON a.ptnum = b.ptnum
  JOIN sos.payors c ON b.payornum = c.payornum
  JOIN sos.providers d ON a.providernum = d.providernum
WHERE
  a.flag = 0
  AND a.dischargedate IS NULL
  AND c.payortype = 'I'
GROUP BY
  c.payorname, d.provcode
ORDER BY
  c.payorname, d.provcode

The version below adds the ROLLUP operator to the GROUP BY. That creates a result set that includes NULL in various cells. Read NULL as “ALL.” Therefore if you see NULL in the ProvCode column, it means that this count is for all providers. There is also a row with NULL in both the Plan and Provider columns, meaning all plans and all providers, which in this case is the number of patients in the result set.

SELECT
  c.payorname AS "Ins Plan",
  d.provcode,
  count(a.ptnum) AS "N"
FROM
  sos.patients a
  JOIN sos.ptpayors b ON a.ptnum = b.ptnum
  JOIN sos.payors c ON b.payornum = c.payornum
  JOIN sos.providers d ON a.providernum = d.providernum
WHERE
  a.flag = 0
  AND a.dischargedate IS NULL
  AND c.payortype = 'I'
GROUP BY
  ROLLUP(c.payorname, d.provcode)
ORDER BY
  c.payorname, d.provcode

CHIP Patients List

Posted on | by SOS Blog | Leave a Comment on CHIP Patients List

We have a state-funded Children’s Health Insurance Program (CHIP) that is offered via a number of commercial insurers.  Like all states, the public money is quickly drying up, so insurers are using third parties to scour their records to find those patients for whom
“erroneous” payments were made.  Aetna is using a company called Aftermath to do this with CHIP patients, but it has been a disaster as the company is making what appears to be serious false assumptions.

We need to identify those CHIP clients under any insurer so that we can check benefits before every visit.

The only way to identify these clients is to have a query that identifies patients younger than 19 who have insurance policies in which they are also the subscriber. The list should include only patients who have received services in the last 120 days.

SELECT DISTINCT
  a.lastname,a.firstname,a.id
FROM
  sos.patients a
  JOIN sos.rv_policies b ON a.ptnum = b.ptnum
  JOIN sos.journal c ON a.ptnum = c.ptnum
WHERE
  sos.AgeInYears(a.dob,today()) < 19
  AND reltoinsd = '01'
  AND c.trantype = 'S'
  AND c.trandate BETWEEN (TODAY() - 120) AND TODAY()
ORDER BY a.lastname,a.firstname,a.id

 

 

Annual Sessions Remaining in Authorization

Posted on | by SOS Blog | Leave a Comment on Annual Sessions Remaining in Authorization

 Is there any way with SOS to track the total sessions a patient has used towards their yearly total?   We know that sessions used towards authorized sessions are tracked but would like to be able to track total sessions for our therapists so that yearly maximums are not gone over.

(Query Contributed by Vince Bellwoar)

We explored this a few years ago and were able to get two birds with one stone.

Under the ADDITIONAL TAB in the schedule reminder box, we use the first two spaces for the annual benefit limit for the year (e.g. 20 if the plan will pay for 20 outpatient sessions).

Seth wrote us a nifty Query that:

(1.) counts the number of sessions used in a certain time period (you can designate the current year or put in the an actual date range).

(2.) Subtracts this amount from the number we inputted under the schedule reminder box in ADDITIONAL screen.

I have included the query below.  The results tell us of anyone with 4 or fewer visits remaining for the year–plenty of time to make a clinical plan.

I said “two birds”.  The other benefit is that by listing the annual benefit limit is the additional tab, our secretaries can quickly find the amount. (We also put copay amount in this area).

Note that once parity goes into full effect by the end of 2009, most insurers will not have an annual benefit limit–unless they try to put an annual benefit limit on how many times a the subscriber can see their primary care doc.

[Additional Comments: There are some commented out options in this SQL code (lines starting with “–“)  that would give you the option of limiting the query to certain listed payor numbers, or all payor numbers other than those listed. As written, neither of these options is enabled, so all payors are included. Vince also has a specified list of cpt codes (not service shorthand codes) to be included in the count. Be sure to tune that list to your requirements. You also can modify the warning threshold from his setting of 4)

SELECT
  a.lastname AS "PtLast name",
  a.firstname AS "First name",
  a."id" AS "Acct ID",
  a.dob,
  h.dxcode,

  (IF ISNUMERIC(e.Fld1) = 1
     THEN CAST(e.Fld1 AS INTEGER)
     ELSE  IF ISNUMERIC(LEFT(a.NoSchedReason,2)) = 1
             THEN CAST(LEFT(a.NoSchedReason,2) AS DECIMAL)
             ELSE 0
             ENDIF
     ENDIF
  ) AS "Annual Benefit Max",

  (SELECT COUNT(*)
   FROM sos.journal jou JOIN sos.jcharges chg ON jou.jnum = chg.jnum JOIN sos.services srv ON chg.servicenum = srv.servicenum
   WHERE jou.ptnum = a.ptnum
     AND srv.cptcode IN
     ('90801','90805','90806','90846','90847','90853','90862') /* CPT LIST HERE */
     AND YEAR(jou.trandate) = YEAR(CURRENT DATE)  /* this line sets date range */
  ) AS "# Visits Used",

  ("Annual Benefit Max" - "# Visits Used") AS "# remaining",
  COALESCE(f.provcode,'None') AS "Primary Prov", c.payorname AS "Carrier", d.insdid AS "Insured ID"
FROM
  sos.patients a
  JOIN sos.ptpayors b ON a.ptnum = b.ptnum
  JOIN sos.payors c ON b.payornum = c.payornum JOIN sos.ptpolicies d ON b.ptpayornum = d.ptpayornum
  LEFT OUTER JOIN sos.uddatapol e ON d.uddatanum = e.uddatanum
  LEFT OUTER JOIN sos.providers f ON a.providernum = f.providernum LEFT OUTER JOIN sos.ptcsu g ON a.ptcsunum = g.ptcsunum
  LEFT OUTER JOIN sos.dx h ON g.dx1 = h.dxnum WHERE a.flag = 0
  --AND c.payornum IN (101,102,103)   /* PAYOR NUMBER LIST HERE TO LIMIT PAYORS TO THOSE LISTED OR USE NEXT*/
  --AND c.payornum NOT IN (101,102,103)   /*PAYOR NUMBER LIST HERE TO EXCLUDE SPECIFIED PAYORS */
  AND a.dischargedate IS NULL
  AND "Annual Benefit Max" > 0
  AND "# remaining" <= 4              /* SPECIFY VISIT THRESHOLD, EG: 4 VISITS OR LESS REMAINING */
  AND b.ptpayornum IN  (SELECT ptpayornum
                        FROM sos.rv_charges
                        WHERE YEAR(trandate) = YEAR(CURRENT DATE)
                        AND ptnum = a.ptnum)

 

Aging Grouped By Insurance Carrier

Posted on | by SOS Blog | Leave a Comment on Aging Grouped By Insurance Carrier

I am looking for an aging report that displays just the insurance payor aging, grouped by carrier. That is, a report that has Insurance Carrier as a group heading with the relevant patient aging within the group.

If you want to export to Excel, you must use DBISQLG. The older DBISQLC version, on the other hand, has a handy TEXT output format that is lacking in the G version. These are Sybase tools and I am at a loss as to why they omitted certain features in the new version. They are written in different programming languages (C vs. Java), so perhaps that explains it.

You could also use FlySpeed SQL Query, which has a feature to export nicely formatted results to PDF or Excel.

SELECT DISTINCT
  a.payorname AS "INS PAYOR",
  (b.lastname +', '+ b.firstname +' / '+ b.ID) AS "Patient",
  SUM(a.balance) AS "BALANCE",
  SUM(a.age0to30) AS "Current",
  SUM(a.age61to90) AS "31 - 60",
  SUM(a.age91to120) AS "91-120",
  SUM(a.ageovr120) AS "OVER 120",
  SUM(a.ageunapplied ) AS "Unapplied",
  COALESCE((SELECT STRING(MAX(lastbilled))
    FROM sos.jchgsplits
    WHERE ptpayornum = a.ptpayornum AND lastbilled IS NOT NULL),'NONE')
    AS "Last Billed"
FROM
  sos.rv_ptpayors a JOIN sos.patients b
WHERE
  a.payortype = 'I'
  AND a.balance > 0
GROUP BY
  "INS PAYOR","Patient",a.ptpayornum

UNION   /* --- COMBINE RESULT SETS OF QUERY ABOVE WITH QUERY BELOW */

SELECT
  a.payorname AS "INS PAYOR",
  'SUBTOTAL',
  SUM(a.balance) AS "BALANCE",
  SUM(a.age0to30) AS "Current",
  SUM(a.age61to90) AS "31 - 60",
  SUM(a.age91to120) AS "91-120",
  SUM(a.ageovr120) AS "OVER 120",
  SUM(a.ageunapplied ) AS "Unapplied",
  ' ' AS "Last Billed"
FROM
  sos.rv_ptpayors a
WHERE
  a.payortype = 'I'
  AND a.balance > 0
GROUP BY
  "INS PAYOR"

UNION  /* --- COMBINE RESULT SETS OF QUERY ABOVE WITH QUERY BELOW - */

SELECT
  'Z-Z-Z--- GRAND TOTALS ---Z-Z-Z',
  ' ',
  SUM(a.balance) AS "BALANCE",
  SUM(a.age0to30) AS "Current",
  SUM(a.age61to90) AS "31 - 60",
  SUM(a.age91to120) AS "91-120",
  SUM(a.ageovr120) AS "OVER 120",
  SUM(a.ageunapplied ) AS "Unapplied",
  ' ' AS "Last Billed"
FROM
  sos.rv_ptpayors a
WHERE
  a.payortype = 'I'
  AND a.balance > 0

/*---- Sort combined result sets by first, then second column ------- */
ORDER BY
  1,2

This query uses additional queries to get subtotals and grand total, then uses the UNION operator to combine the result sets.