Category: Queries – Providers

Fees for Period with Amount Paid by Facility and Provider

Posted on | by sos documentation | Leave a Comment on Fees for Period with Amount Paid by Facility and Provider

We would like to calculate the total fees during a period and the amount paid toward those fees, grouped by Facility and by Provider.

The following query uses the “ROLLUP” option to include subtotals for all providers and all facilities, as well as a grand total. In the result set interpret NULL as “ALL”. For example, if you see NULL in the Provider column, read that as “All providers”.

 

SELECT
  COALESCE(g.shorthand,'None') AS "Facility",
  f.provcode AS "Primary Provider",
  SUM(b.amount) AS "Service Amount",
  SUM(coalesce(i.crsplamt,0)) AS "Total Credit"
FROM
  sos.patients a
  JOIN sos.journal b ON a.ptnum=b.ptnum
  JOIN sos.jcharges c ON b.jnum=c.jnum
  JOIN sos.jchgsplits h ON h.jnum=c.jnum
  LEFT OUTER JOIN sos.jcrsplits i ON h.chgsplnum=i.chgsplnum
  LEFT OUTER JOIN sos.jcredits j ON i.jnum=j.jnum
  JOIN sos.ptcsu d ON c.ptcsunum=d.ptcsunum
  LEFT OUTER JOIN sos.providers f ON a.providernum=f.providernum
  LEFT OUTER JOIN sos.facilities g ON d.facilitynum=g.facilitynum
WHERE
  b.trandate BETWEEN '2008-01-01' AND '2015-02-20'
  AND a.licnum=101
  AND (credtype <> 'adjustment' OR credtype is NULL)
GROUP BY
  ROLLUP("Facility", f.provcode)
ORDER BY
  "Facility", f.provcode

Average Number of Visits by Year, Month, and Provider

Posted on | by sos documentation | Leave a Comment on Average Number of Visits by Year, Month, and Provider
 I’m looking for a query that tells me the AVERAGE number of treatment sessions per patient (as defined by a service code whose “include on claims” box is checked in SERVICES under LOOKUPS) for a date range.  I’d prefer that the query not list every patient but an average of each provider’s patients.
 
SO, given our 75 providers, what is each providers average amount of sessions conducted per patient over a 4 week period. If a provider saw every patient once a week, their average would be 4.  If another provider saw every patient every two weeks, their average would be 2.

Interesting question and (mostly) answered by a simple query. Note that there is built-in error due to mid-month intakes, but if we assume that variable is relatively stable, then this should give you the trend you want.

The data is output by year, month, and provider code. You can set the range to be examined by altering the date range in the WHERE clause. Instead of a plain GROUP BY, this query uses GROUP BY ROLLUP to provide  subtotals. Remember that where you see NULL in the output, read it as “All”. The ROLLUP will then give you averages for the entire period, each year, and each month, as well as by provider and across providers:

 

SELECT   
  YEAR(j.trandate) AS yr,MONTH(j.trandate) AS mon,
  p.provcode,
  count(distinct j.ptnum) AS pts,
  count(distinct j.jnum) AS visits,
  visits/pts AS average
FROM
  sos.journal j 
  JOIN sos.jcharges c ON j.jnum = c.jnum
  JOIN sos.providers p ON c.providernum = p.providernum
  JOIN sos.services s ON c.servicenum = s.servicenum
WHERE
  j.trandate BETWEEN '2000-01-01' AND '2012-12-31'
  AND s.insbillable = 1
GROUP BY ROLLUP
  (yr,mon,provcode)
ORDER BY
  yr,mon,provcode

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

Posted on | by sos documentation | 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")

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

Posted on | by sos documentation | 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")

Count of Intakes for Past 365 Days With Specified Dx

Posted on | by sos documentation | Leave a Comment on Count of Intakes for Past 365 Days With Specified Dx

We need the total number of new (the past 365 days) intakes that were given a primary or secondary diagnosis of 304.4, including sub-diagnoses. In addition to the grand total, we also would like a breakdown by provider.

The following query demonstrates some simple date arithmetic in the WHERE clause to provide a dynamic calculation of the date 365 days ago. In addition, we are using the GROUP BY ROLLUP ( … ) structure to get both the grand total and the subtotals in the same result set. When you run the query you will see a top row where NULL appears in the provider and provcode columns. In this context, whereever you see NULL, it actually should be interpreted as “all”. The first row then shows the number of intakes for “all providers” and “all provcodes,” in other words, the grand total.

SELECT
  UPPER(provlname) + ', ' + provfname AS "Provider",provcode, COUNT(DISTINCT pt.ptnum) as "N"
FROM
  sos.patients pt
  JOIN sos.ptcsu csu ON pt.ptnum = csu.ptnum
  JOIN sos.ptcsudx ptdx ON csu.ptcsunum = ptdx.ptcsunum
  JOIN sos.providers prv ON pt.providernum = prv.providernum
WHERE
  (pt.intakedate >= (TODAY()-365))
  AND (dxcode1 LIKE '304.4%' OR dxcode2 LIKE '304.4%')
GROUP BY
  ROLLUP ("Provider",provcode)