Category: G4 Queries

I noticed a change in the Provider Activity Report by Primary Provider when I recently reprinted it for a previous time period. I was able to track the change to a payment that is on the new report but was not on the old one. I checked the payment in question and it was posted prior to the first report. What is the explanation?

It is probably that the primary provider was not assigned to the account when the original report was run, or that a different provider was assigned at the time. In either case, the payment would not be shown for the current primary provider. Below is a query that shows the current provider as well as the provider at the time of each revision to the patient data. By comparing the two values you can determine if there was a change, when it was made, and who made it.

select 
  a.ptnum,
  a.lastname,
  a.firstname,
  a.id,
  coalesce(c.provcode,'none') as current_prov,
  coalesce(d.provcode,'none') as previous_prov,
  a.upddate as last_update,
  a.updtime as last_updtime,
  b.upddate as revision_date,
  b.updtime as revision_time,
  b.upduser as revision_user
from 
  sos.patients a 
  join sos.atPatients b on a.ptnum = b.ptnum 
  left outer join sos.providers c on a.providernum = c.providernum
  left outer join sos.providers d on b.providernum = d.providernum
where 
  a.ptnum = 101
order by 
  revision_date desc,revision_time desc

I am looking for a report that is like the Charges for Period by Provider – Summary EXCEPT I need it by Primary Provider, not the Billing Provider.   I do not want “0” charges included and a total of all the charges.  I also want it by data set, not totaled across the datasets.

The following query should give you the data you need. This type of ROLLUP query provides grand totals and subtotals, but you have to know a little trick to read it. Wherever you see “(NULL)”, you should interpret it as “ALL”. For example, the first line in the results below is the grand total — ALL datasets, ALL Providers, and ALL services. The second line is the first dataset only, but ALL Providers, and ALL services. The third line is for only the first dataset, and only the first provider, but ALL services. So, although the format might seem a bit strange at first, I think all the information you want is presented, including the desired subtotals for each dataset, provider, and service. Note that, as requested, this query sorts by primary provider, not rendering provider.

SELECT
  a.licnum AS "Dataset",
  b.provlname + '(' + b.provcode +')' AS "Provider",
  e.srvdesc + '('+ e.srvcode +')' AS "Service",
  COUNT(a.ptnum) AS "Patient Count",
  COUNT(c.jnum) AS "Number of Charges",
  SUM (c.amount) AS "Total Fees",
  SUM (d.units) AS "Total Units"
FROM
  sos.patients a
  JOIN sos.providers b ON a.providernum = b.providernum
  JOIN sos.journal c ON a.ptnum = c.ptnum
  JOIN sos.jcharges d ON c.jnum = d.jnum
  JOIN sos.services e ON d.servicenum = e.servicenum
WHERE
  c.amount > 0
  AND c.trandate BETWEEN '2015-01-01' AND '2015-03-31'
GROUP BY 
  ROLLUP("Dataset","Provider","Service")
ORDER BY
  "Dataset","Provider","Service"

We are completing the NMHS Survey and need to run a query that gives us a break down of the following patient information for April 2014:  total male and female clients; clients aged 0-17, 18-64, & 65 and older; totals for ethnicity (coded in patient UD field 25 (including “Unknown”); and totals for race (including “Unknown”). I would need these statistics for just those patients seen for services during a particular date range.

This is not an unusual request. The query below uses two interesting techniques through which we combine several different small queries using, the UNION syntax, to create the final result set. In addition, in each of the small queries we have a subquery that provides a list of patients who were seen in the desired date range. That list is used in each of the WHERE clauses to limit the population on which the totals are based.  Note that when you use UNION, there can be only one ORDER BY, and it must be on the last query. In addition, unlike with standalone queries, you ORDER by column number instead of column name. Another essential when using UNION is that each of the smaller queries must have the same number of columns and the data type of each column must be the same in each of the queries you are UNIONing.

IMPORTANT — be sure to adjust the date range in every one of the queries! In this example there are five demographic queries and each one includes the date range that you will have to adjust.

--- clients seen for period
SELECT
   '** Clients Seen' AS "Description",
   '' AS "Group",
   COUNT(DISTINCT c.ptnum) AS "Totals"
FROM
   sos.patients c
WHERE
   ptnum IN (SELECT ptnum 
             FROM sos.journal a 
             WHERE trantype = 'S' AND trandate BETWEEN '2014-04-01' AND '2014-04-30' AND amount > 0)


--- Racial Identity
UNION
SELECT
   '3. Racial Identity' AS "Description",
   (IF COALESCE(c.fld6,'') = '' THEN 'Unknown' ELSE c.fld6 END IF) AS "Group",
   COUNT(*) AS "Totals"
FROM
   sos.patients b LEFT OUTER JOIN sos.uddatapt c ON b.uddatanum = c.uddatanum
WHERE
   ptnum IN (SELECT ptnum 
             FROM sos.journal  
             WHERE trantype = 'S' AND trandate BETWEEN '2014-04-01' AND '2014-04-30' AND amount > 0)
GROUP BY "Group"


--- Ethnicity
UNION
SELECT
   '4. Ethnic Identity' AS "Description",
   (IF COALESCE(c.fld25,'') = '' THEN 'Unknown' ELSE c.fld25 END IF) AS "Group",
   COUNT(*) AS "Totals"
FROM
   sos.patients b LEFT OUTER JOIN sos.uddatapt c ON b.uddatanum = c.uddatanum
WHERE
   ptnum IN (SELECT ptnum 
             FROM sos.journal  
             WHERE trantype = 'S' AND trandate BETWEEN '2014-04-01' AND '2014-04-30' AND amount > 0)
GROUP BY  "Group"

--- Age Group
UNION
SELECT
   '2. Age Group' AS "Description",
   (CASE 
      WHEN sos.AGEINYEARS(c.dob,'2014-04-30') BETWEEN 0 AND 17 THEN '0-17 yrs'
      WHEN sos.AGEINYEARS(c.dob,'2014-04-30') BETWEEN 18 AND 64 THEN '18-64 yrs'
      WHEN sos.AGEINYEARS(c.dob,'2014-04-30') > 64 THEN '65+ yrs'
      ELSE 'unknown'
    END) AS "Group",
   COUNT(*) AS "Totals"
FROM
   sos.patients c 
WHERE
ptnum IN (SELECT ptnum 
          FROM sos.journal  
          WHERE trantype = 'S' AND trandate BETWEEN '2014-04-01' AND '2014-04-30' AND amount > 0)
GROUP BY "Group"

---- gender
UNION
SELECT
   '1. Gender' AS "Description",
   COALESCE(sex,'unknown') AS "Group",
   COUNT(*) AS "Totals"
FROM
   sos.patients c 
WHERE
ptnum IN (SELECT ptnum 
          FROM sos.journal  
          WHERE trantype = 'S' AND trandate BETWEEN '2014-04-01' AND '2014-04-30' AND amount > 0)
GROUP BY "Group"
ORDER BY 1,2