Category: G4 Queries

This query produces a list of cities and the number of patients in each one who have been seen during a specified date range.

SELECT b.city, count(distinct a.ptnum) as "Count"
FROM patients a JOIN payors b ON a.payornum = b.payornum
WHERE a.ptnum IN (SELECT ptnum FROM journal 
                  WHERE trantype = 'S' 
                  AND trandate BETWEEN '2012-11-01' AND '2013-06-30')
GROUP BY b.city
ORDER BY b.city

This is a very simple query that lists all active patients along with all ICD-10 diagnoses in their professional claim setups.

 

SELECT 
  pt.id AS "ID",
  pt.lastname AS "Last Name", 
  pt.firstname AS "First Name",
  pt.dob AS "Date of Birth",
  pt.socsec AS "SSN",
  dx1.dxcode AS "Dx1",
  dx2.dxcode AS "Dx2", 
  dx3.dxcode AS "Dx3", 
  dx4.dxcode AS "Dx4", 
  dx5.dxcode AS "Dx5", 
  dx6.dxcode AS "Dx6", 
  dx7.dxcode AS "Dx7", 
  dx8.dxcode AS "Dx8", 
  dx9.dxcode AS "Dx9",
  dx10.dxcode AS "Dx10",
  dx11.dxcode AS "Dx11",
  dx12.dxcode AS "Dx12" 
FROM 
  patients pt
  JOIN ptcsu on pt.ptnum=ptcsu.ptnum
  LEFT OUTER JOIN dx dx1  on ptcsu.icd10dx1=dx1.dxnum
  LEFT OUTER JOIN dx dx2  on ptcsu.icd10dx2=dx2.dxnum
  LEFT OUTER JOIN dx dx3  on ptcsu.icd10dx3=dx3.dxnum
  LEFT OUTER JOIN dx dx4  on ptcsu.icd10dx4=dx4.dxnum
  LEFT OUTER JOIN dx dx5  on ptcsu.icd10dx5=dx5.dxnum
  LEFT OUTER JOIN dx dx6  on ptcsu.icd10dx6=dx6.dxnum
  LEFT OUTER JOIN dx dx7  on ptcsu.icd10dx7=dx7.dxnum
  LEFT OUTER JOIN dx dx8  on ptcsu.icd10dx8=dx8.dxnum
  LEFT OUTER JOIN dx dx9  on ptcsu.icd10dx9=dx9.dxnum
  LEFT OUTER JOIN dx dx10  on ptcsu.icd10dx10=dx10.dxnum
  LEFT OUTER JOIN dx dx11  on ptcsu.icd10dx11=dx11.dxnum
  LEFT OUTER JOIN dx dx12  on ptcsu.icd10dx12=dx12.dxnum
WHERE 
  pt.flag = 0
ORDER BY 
  lastname,firstname,"ID"

 

Need a query which lists:
Patient Name (lname, fname), ID, DOS, Date of Adjustment, Amount of adjustment, Payor Category (patient, insurance or other) for:

• Any patient with 2015 date of service who has any adjustment made in 2016.
• some patients will have adjustments in 2015 but I only need to see those patients and DOS with adjustments made in 2016
• some patients will have one DOS with multiple adjustments, that’s OK, each adjustment and date of adjustment should show on a separate line.

This query illustrates the use of a CASE expression and the date function YEAR.

 

SELECT
  lastname,
  firstname,
  "ID" ,
  srv_date,
  cre_date AS adj_date,
  crsplamt AS adj_amt,
  adjcode,
  (CASE 
     WHEN payortype = 'I' THEN 'Insurance'
     WHEN payortype = 'S' THEN 'Patient'
     ELSE 'Other'
   END) AS 'Payer Category'
FROM
  rv_creditsplits
WHERE
  credtype = 'adjustment'
  AND YEAR(srv_date) = 2015
  AND YEAR(cre_date) = 2016 
ORDER BY
  lastname,firstname,"id",srv_date

I am looking for a report that will show me the % of each insurance payer seen each month as well as to have the ability to break that number down by % of insurance payer seen by individual providers each month. For example, United health care is what % or my business in the month of July.

If we were to simply look at amounts rather than percentages, this request could be met by a pretty simple query, using the ROLLUP feature:

SELECT
  (IF a.payortype = 'I' THEN a.payorname ELSE 'NonIns' ENDIF) AS "Payer",
  a.provcode AS "Provider",
  SUM(a.crsplamt) AS "Paid"
FROM
  sos.rv_creditsplits a
WHERE
  a.cre_date BETWEEN '2000-01-01' AND '2016-12-31'
  AND  a.credtype IN ('check','cash','charge')
  AND a.licnum = 101    /*only patients in the main data set*/
GROUP BY 
  ROLLUP("Payer","Provider")
ORDER BY 
  "Payer","Provider";

The results would look like the figure below. “(NULL)” should be read as “All”, so the first line is “All payers and all providers paid 658,699.78.” The second line is “Payer: AARP (2) for all providers paid $732.28.” The next three lines show the amount paid by the same payer for each of the three providers, 006, AT, and PPP.

The need for a percent, however, means that we must have the relevant total always available for the current payer. That means we will have to insert a subquery in the SELECT list, either as a displayed column or as part of a much more complex expression used to calculate the percentage. For readability sake, let’s separate out the calculation of the total into a “PayerTotal” column of its own. We can now also add a Percent column that is simply the Paid amount divided by the PayerTotal, multiplied by 100.

SELECT
  (IF a.payortype = 'I' THEN a.payorname ELSE 'NonIns' ENDIF) AS "Payer",
  a.provcode AS "Provider",
  SUM(a.crsplamt) AS "Paid",
  (SELECT SUM(crsplamt) FROM sos.rv_creditsplits   
   WHERE cre_date BETWEEN '2000-01-01' AND '2016-12-31' 
         AND credtype IN ('check','cash','charge')
         AND licnum = 101 
         AND (IF payortype = 'I' THEN payorname ELSE 'NonIns' ENDIF) = "Payer")  AS "PayerTotal", 
  ( ("Paid"/"PayerTotal") * 100) AS "Percent" FROM sos.rv_creditsplits a 
WHERE 
  a.cre_date BETWEEN '2000-01-01' AND '2016-12-31' 
  AND  a.credtype IN ('check','cash','charge') 
  AND a.licnum = 101    /*only patients in the main data set*/ 
GROUP BY 
  ROLLUP("Payer","Provider")
  ORDER BY "Payer","Provider";

These changes give us pretty much what we want, but we can add some code to round the percent values to two decimal places. In addition, we should add an IF condition to prevent a divide-by-zero error if the PayerTotal is zero. The final query becomes:

SELECT
  (IF a.payortype = 'I' THEN a.payorname ELSE 'NonIns' ENDIF) AS "Payer",
  a.provcode AS "Provider",
  (SELECT SUM(crsplamt) 
   FROM sos.rv_creditsplits 
   WHERE cre_date BETWEEN '2000-01-01' AND '2016-12-31' 
      AND credtype IN ('check','cash','charge') 
      AND licnum = 101 
      AND (IF payortype = 'I' THEN payorname ELSE 'NonIns' ENDIF) = "Payer") AS "PayerTotal",
  SUM(a.crsplamt) AS "Paid", 
  (IF PayerTotal > 0 THEN CAST( (("Paid"/"PayerTotal") * 100) AS DECIMAL(6,2)) ELSE 0 ENDIF) AS "Percent" 
FROM 
  sos.rv_creditsplits a 
WHERE 
  a.cre_date BETWEEN '2000-01-01' AND '2016-12-31' /*desired date range*/ 
  AND a.credtype IN ('check','cash','charge') /*only payments - no adjustments*/ 
  AND a.licnum = 101 /*only patients in the main data set*/ 
GROUP BY 
  ROLLUP("Payer","Provider") 
ORDER BY "Payer","Provider";

It is important to note that to effectively change the date range for the query, you must change the date range in BOTH the main query and the subquery.

If you prefer percentages for each provider rather than for each payer, that can be accomplished with just a little editing of the subquery, the GROUP BY and the ORDER BY, like so:

SELECT
  a.provcode AS "Provider",
  (IF a.payortype = 'I' THEN a.payorname ELSE 'NonIns' ENDIF) AS "Payer",
  (SELECT SUM(crsplamt) 
   FROM sos.rv_creditsplits 
   WHERE cre_date BETWEEN '2000-01-01' AND '2016-12-31' 
     AND credtype IN ('check','cash','charge')
     AND licnum = 101 
     AND provcode = "Provider")  AS "ProvTotal",
  SUM(a.crsplamt) AS "Paid",
  (IF "ProvTotal" > 0 
     THEN CAST( (("Paid"/"ProvTotal") * 100) AS DECIMAL(6,2)) 
     ELSE 0 
     ENDIF) AS "Percent"
FROM
  sos.rv_creditsplits a
WHERE
  a.cre_date BETWEEN '2000-01-01' AND '2016-12-31'  /*desired date range*/
  AND  a.credtype IN ('check','cash','charge')     /*only payments - no adjustments*/
  AND a.licnum = 101    /*only patients in the main data set*/
GROUP BY 
  ROLLUP("Provider","Payer")
ORDER BY 
  "Provider","Payer";

The results are now percentages of provider totals rather than payer totals