Category: G4 Queries

Count of Intakes by Year and Month

Posted on | by SOS Blog | Leave a Comment on Count of Intakes by Year and Month

I would like a simple count of the number of intakes each month.

Easy enough. The GROUP BY contains the ROLLUP keyword which gives sub and grand totals as well as the monthly counts. Wherever you see “NULL” in the result set, think “All”. The WHERE clause restrict the output to the past two years (730 days). You can easily change that to a date range or different number of days if you like.

SELECT
  YEAR(pt.intakedate) AS "YEAR", 
  MONTH(pt.intakedate) AS "MONTH", 
  COUNT(DISTINCT pt.ptnum) AS "N"
FROM
  sos.patients pt
WHERE
  (pt.intakedate >= (TODAY()-730))
GROUP BY
  ROLLUP ("YEAR","MONTH")
ORDER BY -"YEAR", -"MONTH"

Appointment Count by DataSet, Resource, and Month

Posted on | by SOS Blog | Leave a Comment on Appointment Count by DataSet, Resource, and Month

This query provides appointment counts by DataSet, Resource and Month. Only non-cancelled appointments for existing patients are included in the count. The use of GROUP BY ROLLUP provides us with grand total and subtotals as well as the detail for each month. Wherever you see NULL rather than one of the categories, read it as “ALL”. For example, if you have NULL in the DataSet column, that means the total is for all DataSets.

 

 -- appt count BY dataset, resource, AND month
SELECT
  licnum AS "DataSet",
  resourcecode, 
  YEAR(apptdate) AS "Year", 
  MONTH(apptdate) AS "Month", 
  COUNT(*) AS "Number"
FROM
  sos.rv_appts
WHERE
  apptdate BETWEEN '2010-06-01' AND '2011-07-28' 
  AND ptnum IS NOT NULL 
  AND cancelflag <> 1
GROUP BY ROLLUP
  ("DataSet",ResourceCode, "Year", "Month")
ORDER BY
  "DataSet",ResourceCode, "Year", "Month"

Average Number of Visits by Year, Month, and Provider

Posted on | by SOS Blog | 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

Referral Source Net Revenue

Posted on | by SOS Blog | Leave a Comment on Referral Source Net Revenue

I need a query which gives me (a) a monthly revenue total and (b) total reversals, such as refunds, for patients with a specific referral source, regardless of provider.

i.e.,  May: $10,000 revenue, $2,000 neg adjustments for all patients with referral source Dr. X (from all datasets).

This is a pretty straightforward little query, but it shows the use of the aggregate function SUM, the date functions YEAR and MONTH, and the use of the LIKE expression to get a match with just part of the referral source’s last name. Note that the WHERE condition is looking only at the referral (last) name field. In this example, the result set would include any referral sources with an “X” in the (last) name field. Also, be sure to adjust the date range to match your need.

SELECT
  a.licnum AS "DataSet", 
  YEAR(c.trandate) AS "YR",
  MONTH(c.trandate) AS "MON",
  SUM((IF c.amount > 0 THEN c.amount ELSE 0 ENDIF)) AS "TotRevenue",
  SUM((IF c.amount < 0 THEN c.amount ELSE 0 ENDIF)) AS "TotReversals",
  "TotRevenue" + "TotReversals" AS "NetRevenue"
FROM 
   sos.patients a
   JOIN sos.refsrcs b ON a.refsrcnum = b.refsrcnum
   JOIN sos.journal c ON a.ptnum = c.ptnum
   JOIN sos.jcredits d ON c.jnum = d.jnum
WHERE 
   d.credtype IN ('cash','check','charge','other')
   AND c.trandate BETWEEN '2012-01-01' AND '2012-12-31'
   AND b.refname LIKE '%X%'
GROUP BY
   DataSet,YR,MON
ORDER BY  
   DataSet,YR,MON

Account Aging Balance Detail on Specified Date

Posted on | by SOS Blog | Leave a Comment on Account Aging Balance Detail on Specified Date

I want to see a detailed view of the balance for a particular account on a previous date. The aging report gives me the balance on that date, but I want to see the detail for each item that contributes to the aggregate balance figure.

I think this may give you what you need. You will notice on line 15 (just before the WHERE):

 –,*

 If you remove the dashes at the beginning of that line, it will add many more columns in the output. You can pick and choose from those to add above that line, then put the dashes back in to hide the extra columns. In the WHERE, just change the ID to the account you want to check out.

This query will list only those chargesplits that were still carrying a balance on June 30, 2012. For any other date, change the dates in both line 13, 14, and line 29.

select 
  a.jnum,
  a.id,
  a.trandate as "service date",
  a.srvcode,
  a.amount as "fee",
  d.payorname as "responsible payor",
  a.chgspldate as "chg split date",
  a.chgsplamt as "chg split amt",
  (select 
      coalesce(sum(crsplamt),0) from sos.rv_creditsplits 
   where 
      cre_date < '2012-07-01'  
      and dateapplied < '2012-07-01' 
      and chgsplnum = a.chgsplnum) as "credits applied",
  a.chgsplamt - "credits applied"  as "chg split balance",
  b.crsplamt as "credit split amt",
  b.credtype as "credit type", 
  b.dateapplied as "date applied"
 --,*
from 
  sos.rv_charges a 
  left outer join sos.rv_creditsplits b on a.chgsplnum = b.chgsplnum
  join sos.ptpayors c on a.ptpayornum = c.ptpayornum
  join sos.payors d on c.payornum = d.payornum
where 
  a.id = '123456789'
  and "chg split balance" <> 0
  and a.chgspldate < '2012-07-01'
order by 
  "service date","chg split date"