Category: G4 Queries

Count of Referrals by Primary Provider and Referral Source, for Intake Date Range

Posted on | by SOS Blog | 2 Comments on Count of Referrals by Primary Provider and Referral Source, for Intake Date Range

Show number of referrals by referral source, sorted by provider, for a specified intake date range.

This query illustrates the use of an IF…ELSE…ENDIF expression in the SELECT list.

 

SELECT
  COALESCE(c.provcode,'No Pri Prov') AS "Primary Provider",
  IF a.refsrcnum IS NULL
    THEN 'No Ref'
    ELSE TRIM(b.Refname + ' '+b.firstname)
  ENDIF AS "Source Name",
  COALESCE(b.reftype,'') AS "Ref Type",
  COUNT(*) AS "Referrals"
FROM
  sos.patients a
  LEFT OUTER JOIN sos.refsrcs b ON a.refsrcnum = b.refsrcnum
  LEFT OUTER JOIN sos.providers c ON a.providernum = c.providernum
WHERE
  a.intakedate BETWEEN '2000-01-01' AND '2008-08-20'
  AND a.licnum = 101
GROUP BY "Primary Provider","Source Name", "Ref Type"
ORDER BY "Primary Provider","Source Name"

It is also possible to report the data by Rendering Provider, but because a single patient may be seen by two or more (rendering) providers, the grand total will likely be greater than the number of patients in the intake date range. Here is that variation of the query:

SELECT
  DISTINCT COALESCE(c.provcode,'No Pri Prov') AS "Rendering Provider",
  IF a.refsrcnum IS NULL
    THEN 'No Ref'
    ELSE TRIM(b.Refname + ' '+b.firstname)
  ENDIF AS "Source Name",
  COALESCE(b.reftype,'') AS "Ref Type",
  COUNT(*) AS "Referrals"
FROM
  sos.patients a
  LEFT OUTER JOIN sos.refsrcs b ON a.refsrcnum = b.refsrcnum
  LEFT OUTER JOIN sos.jcharges d ON a.ptnum = d.ptnum 
  LEFT OUTER JOIN sos.providers c ON d.providernum = c.providernum
WHERE
  a.intakedate BETWEEN '2000-01-01' AND '2008-08-20'
  AND a.licnum = 101
GROUP BY "Rendering Provider","Source Name", "Ref Type"
ORDER BY "Rendering Provider","Source Name"

Count Of Patients For An Age Range

Posted on | by SOS Blog | Leave a Comment on Count Of Patients For An Age Range

The following will give you a count of patients with activity in calendary year 2006 who were between 13 and 19 on the last day of 2006.

SELECT
  COUNT(DISTINCT a.ptnum) AS "numpts"
FROM
  sos.journal a
  JOIN sos.patients b ON a.ptnum = b.ptnum
WHERE
  trantype = 's'
  AND amount > 0
  AND trandate BETWEEN '2006-01-01' AND '2006-12-31'
  AND sos.AgeInYears(dob,'2006-12-31') BETWEEN 13 AND 19

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 sos.ptpayors s1 JOIN sos.payors s2 ON s1.payornum = s2.payornum JOIN sos.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
sos.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

In Collection Balances by Provider, Age Group, and Diagnosis

Posted on | by SOS Blog | Leave a Comment on In Collection Balances by Provider, Age Group, and Diagnosis

Would it be possible to create a query to show total due from collections accounts by primary provider, primary diagnostic code, and age groups of the patient…. 1-5; 6-12; 13-18; 19-28; 29-40; 41 to 60; above 60?

This query demonstrates several interesting features. The first is the use of a CASE expression to evaluate the appropriate age group for each patient, using a custom function, AGEINYEARS( ) that we include with the SOS database. Next is the use of COALESCE( ) to return a desired string (‘None’) when no primary provider or diagnosis has been entered. Finally, we use the GROUP BY ROLLUP(     ) structure to provide us with all possible totals. When examining the result set, you will see NULL under one or more columns on some rows. When reading the results, you should interpret NULL as meaning “ALL”. Therefore, the row that shows:

Primary Provider  = NULL
AgeGroup   = NULL
PrimaryDx = NULL
Balance Due = 100,000

should be read as the grand total of all balances (all age groups, all providers, and all Dx codes)

 

SELECT
  COALESCE(d.provcode,'None') AS "PrimaryProvider",
  (CASE
     WHEN sos.AGEINYEARS(c.dob,TODAY() ) BETWEEN 0 AND 5 THEN 'Age under 5'
     WHEN sos.AGEINYEARS(c.dob,TODAY() ) BETWEEN 6 AND 12 THEN 'Age 6 - 12'
     WHEN sos.AGEINYEARS(c.dob,TODAY() ) BETWEEN 13 AND 18 THEN 'Age 13 - 18'
     WHEN sos.AGEINYEARS(c.dob,TODAY() ) BETWEEN 19 AND 28 THEN 'Age 19 - 28'
     WHEN sos.AGEINYEARS(c.dob,TODAY() ) BETWEEN 29 AND 40 THEN 'Age 29 - 40'
     WHEN sos.AGEINYEARS(c.dob,TODAY() ) BETWEEN 41 AND 60 THEN 'Age 41 - 60'
     WHEN sos.AGEINYEARS(c.dob,TODAY() ) > 60 THEN 'Age above 60'
     ELSE 'Age unknown'
  END CASE) AS "AgeGroup",
  COALESCE(e.dxcode1,'None') AS "PrimaryDx",
  SUM(b.balance) AS "Balance Due"
FROM
  sos.payors a
  JOIN sos.ptpayors b ON a.payornum = b.payornum
  JOIN sos.patients c ON b.ptnum = c.ptnum
  LEFT OUTER JOIN sos.ptcsudx e ON c.ptcsunum = e.ptcsunum
  LEFT OUTER JOIN sos.providers d ON c.providernum = d.providernum
WHERE
  b.balance > 0
  AND a.collectdate IS NOT NULL
GROUP BY ROLLUP( "PrimaryProvider", "AgeGroup", "PrimaryDx")
ORDER BY "PrimaryProvider", "AgeGroup", "PrimaryDx"