Category: G4 Queries

Adjustment Analysis by Month and Payor

Posted on | by SOS Blog | Leave a Comment on Adjustment Analysis by Month and Payor

We are trying to nail down the source of unusually high adjustments for a period. We want to know the amount billed, amount paid, amount adjusted, portion (%) adjusted for each month and payor, grouped by adjustment code.

This query illustrates the aggregate function SUM, as well as imbedded IF expressions and COALESCE expressions to eliminate NULLs. This is the kind of result set that you might want examine in different orders, so just shuffle the columns in the SELECT list, and in the GROUP BY and ORDER BY clauses as you wish. The GROUP BY and ORDER BY lists should match to prevent confusion when examining the results.

 

SELECT
   (IF a.payortype = 'I' THEN 'INS' ELSE 'PRIVATE' END IF) AS "Payor Type",
   a.adjcode AS "AdjCode", 
   YEAR(a.srv_date) AS "Year",
   MONTH(a.srv_date) AS "Month",
   (UPPER(a.payorname) + ' ' + a.payorfname) AS "Payor",
   SUM (b.chgsplamt) AS "Total Billed",
   SUM((IF a.credtype <> 'Adjustment' THEN COALESCE(a.crsplamt,0) ELSE 0 END IF)) AS "Payments",
   SUM((IF a.credtype = 'Adjustment' THEN a.crsplamt ELSE 0 END IF)) AS "Adjustments" ,
   (IF "Adjustments" IS NOT NULL AND "Total Billed" IS NOT NULL AND "Total Billed" <> 0 
      THEN CAST(("Adjustments"/"Total Billed" * 100)AS DECIMAL(6,2))
   ELSE 0
   END IF)  AS "% Adjusted" 
FROM 
   sos.rv_creditsplits a
   JOIN sos.jchgsplits b ON a.chgsplnum = b.chgsplnum
WHERE
   a.crsplamt IS NOT NULL 
   AND "AdjCode" IS NOT NULL
   AND a.srv_date BETWEEN '2001-01-01' AND '2001-12-31'
GROUP BY 
   "PayorType","AdjCode","Year","Month","Payor"
ORDER BY
   "PayorType","AdjCode","Year","Month","Payor"

Count of Unique Patients for Period, Payor, and Service

Posted on | by SOS Blog | Leave a Comment on Count of Unique Patients for Period, Payor, and Service

I would like to determine the number of unique patients seen for particular services billed to a designated set of payors.

This query can deliver the goods for one or more services and one or more payors. Substitute your desired list of service codes and payor numbers, separating items with commas as in the example below. You can have just one item in the lists or as many as you like. The GROUP BY ROLLUP structure allows you to see subtotals and the grand total in addition the number of patients for each combination. Wherever you see (NULL) in the result set, just interpret it as meaning “ALL”.

SELECT
   b.payornum, a.srvcode,COUNT(DISTINCT a.ptnum) AS "Clients Seen"
FROM
   sos.rv_charges a
   JOIN sos.ptpayors b ON a.ptpayornum = b.ptpayornum
WHERE
   amount > 0
   AND trandate BETWEEN '2011-01-01' AND '2011-12-31'
   AND srvcode IN ('OV','CD')
   AND b.payornum IN (111, 611)
GROUP BY ROLLUP
   (b.payornum,a.srvcode)

Service Count, Total Charges, and Applied Payments by Provider and Patient Category

Posted on | by SOS Blog | Leave a Comment on Service Count, Total Charges, and Applied Payments by Provider and Patient Category

We would like to know the income generated for services based on provider and patient category.

The following query shows these results in a “ROLLUP” grid, displaying both detail down to the service code level as well as subtotals and grand totals. When you run the query and examine the results, you should interpret “NULL” in the Provider, PtCategory, and Service columns as meaning “ALL”. For example, the row that shows NULL in all three of these columns is showing the totals for ALL providers, ALL ptcategories, and ALL service codes — in other words, the grand totals. A row that shows a specific provider and a specific category, but NULL in the Service column provides the subtotal of all service codes for the indicated provider and category. You could limit the result set by replacing the wildcard percent sign in the WHERE clause with a specific provider code and/or patient category code if you like.

SELECT
   (a.provfname + ' '+a.provlname) AS "Provider",
   c.categcode AS "PtCategory",
   a.srvcode AS "Service",
   COUNT(DISTINCT jnum) AS "Srv Count",
   SUM(a.chgsplamt) AS "TotalCharges",
   SUM(COALESCE((SELECT SUM(COALESCE(crsplamt,0)) FROM sos.rv_creditsplits
   WHERE  chgsplnum = a.chgsplnum AND credtype IN ('CASH','CHECK','CHARGE')),0)) AS "TotalPayments"
FROM
   sos.rv_charges a
   JOIN sos.patients b ON a.ptnum = b.ptnum
   LEFT OUTER JOIN sos.ptcategs c ON b.ptcategnum = c.ptcategnum
WHERE
   a.trandate BETWEEN '1980-01-01' AND '2008-12-31'
   AND a.provcode LIKE '%'
   AND "PtCategory" LIKE '%'
GROUP BY
  ROLLUP ("Provider","PtCategory","Service")

Appointment Count by Patient Category and Appointment Type

Posted on | by SOS Blog | Leave a Comment on Appointment Count by Patient Category and Appointment Type

We need a report that provides total number of a particular type of appointment by pt category for a specified period.

Be sure to adjust the date range in the WHERE clause for your desired period.

This query uses the GROUP BY ROLLUP (  ) statement to give grand and subtotals as well as the results for each specific patient category, provider type, and service code combination. Wherever you see NULL in the result set, interpret as “ALL”.

SELECT
  COALESCE(d.categcode,'None') AS "PtCategory",
  COALESCE(f.ApptType,'None') AS "ApptType",
  COUNT(distinct a.detailnum) AS ApptCount
FROM
  sos.appt_d a
  JOIN sos.patients c ON a.ptnum = c.ptnum
  LEFT OUTER JOIN sos.ptcategs d ON c.ptcategnum = d.ptcategnum 
  LEFT OUTER JOIN sos.appttypes f ON a.appttypenum = f.appttypenum
WHERE 
  a.adate BETWEEN '2012-01-01' AND '2012-12-31'
  AND c.licnum = 101
GROUP BY ROLLUP ("PtCategory","ApptType")

Service Count and Units By Patient Category, Provider Type, and Service Code

Posted on | by SOS Blog | Leave a Comment on Service Count and Units By Patient Category, Provider Type, and Service Code

We need a report that provides units provided/charges for a specified patient category, broken down by provider type and service code for a specified date range from both active and inactive clients in dataset 101.

Be sure to adjust the date range in the WHERE clause for your desired period.

This query uses the GROUP BY ROLLUP (  ) statement to give grand and subtotals as well as the results for each specific patient category, provider type, and service code combination. Wherever you see NULL in the result set, interpret as “ALL”.

SELECT
  COALESCE(d.categcode,'None') AS "PtCategory",
  COALESCE(f.provtypecode,'None') AS "ProvType",
  g.srvcode AS "ServiceCode",
  COUNT(distinct b.jnum) AS ServiceCount,
  SUM(b.units) AS TotalUnits
FROM
  sos.journal a
  JOIN sos.jcharges b ON a.jnum = b.jnum
  JOIN sos.patients c ON a.ptnum = c.ptnum
  LEFT OUTER JOIN sos.ptcategs d ON c.ptcategnum = d.ptcategnum
  JOIN sos.providers e ON b.providernum = e.providernum
  JOIN sos.services g ON b.servicenum = g.servicenum
  LEFT OUTER JOIN sos.provtype f ON e.provtypenum = f.provtypenum
WHERE
  a.trandate BETWEEN '2012-01-01' AND '2012-12-31'
  AND a.amount > 0
  AND c.licnum = 101
GROUP BY ROLLUP ("PtCategory","ProvType","ServiceCode")