Category: Queries – Providers

Service Count By Provider, Patient Category and Service Code For Period

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

I need total # of services rendered broken down by provider, patient category, and service code, for a specific time frame.

SELECT
(a.provfname + ' '+a.provlname) AS "Provider",
c.categcode AS "Category",
a.srvcode AS "Service",
COUNT(DISTINCT jnum) AS "Srv Count"
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'
GROUP BY
"Provider","Category","Service"

Projected Collections for Period by Site

Posted on | by SOS Blog | Leave a Comment on Projected Collections for Period by Site

We go through our daily schedules each day to look at the amount of money to be collected from the patients. The amount is the amount of outstanding non-insurance balance plus current copayment. Would it be possible to write a query that does this?

The query would calculate amounts for each office site using the red “Sites” in the optional tab of the Scheduler. It would provide the total copayment due, non-insurance balance due, and the sum of these two values.

A Grand Total of all sites would be great as well.

This is the same query executed with grouping for site specific totals, and without grouping for the grand totals. The two queries have the same exact structure, so we can use UNION to combine the two query results in a single result set.

SELECT
COALESCE(SiteCode,'None Specified') AS "SITE",
SUM(COALESCE((SELECT ptbalance FROM sos.pt_noninsbalance WHERE ptnum = a.ptnum),0)) AS "PRIOR BAL",
SUM(COALESCE(copayamt,0)) AS "COPAY",
"PRIOR BAL" + "COPAY" AS "PAYMENT DUE"
FROM sos.rv_appts a
WHERE a.apptdate BETWEEN '2002-01-01' AND '2002-12-31'
  AND a.cancelflag = 0
GROUP BY "SITE"

UNION

SELECT
'ALL SITES',
SUM(COALESCE((SELECT ptbalance FROM sos.pt_noninsbalance WHERE ptnum = a.ptnum),0)) AS "PRIOR BAL",
SUM(COALESCE(copayamt,0)) AS "COPAY",
"PRIOR BAL" + "COPAY" AS "PAYMENT DUE"
FROM sos.rv_appts a
WHERE a.apptdate BETWEEN '2002-01-01' AND '2002-12-31'
  AND a.cancelflag = 0

Payments By Provider For Two Periods

Posted on | by SOS Blog | Leave a Comment on Payments By Provider For Two Periods

This query is interesting in that here we use correlated subqueries in the SELECT list to produce the subtotals for columns 2 and 3. 

SELECT
  provcode,
  (SELECT COALESCE(sum(crsplamt),0) FROM sos.rv_creditsplits
        WHERE providernum = a.providernum
        AND credtype IN ('cash','check','other')
        AND dateapplied BETWEEN '2000-01-01' AND '2000-12-31') AS "Per 1 Payments",
  (SELECT COALESCE(sum(crsplamt),0) FROM sos.rv_creditsplits
        WHERE providernum = a.providernum
        AND credtype IN ('cash','check','other')
        AND dateapplied BETWEEN '2001-01-01' AND '2001-12-31') AS "Per 2 Payments"
FROM
  sos.providers a
WHERE
  a.providernum > 100
  AND a.hiderow <> 1
ORDER BY
  provcode

Patients with Diagnosis in Any Position, By Provider

Posted on | by SOS Blog | Leave a Comment on Patients with Diagnosis in Any Position, By Provider

We have been asked to give statistics regarding how many dual diagnosis clients we treat during a specific time period. I can run a report requesting the primary diagnosis; however, at times I need to run a report on one specific diagnosis that may be listed as #2, 3 or even 4.

SELECT
  Providers. ProvCode, Providers.ProvLName, Providers.ProvFName,
  Patients.LastName, Patients.FirstName, Patients.IntakeDate,
  PtVars.LFeeDate AS "Last Service",
  PtCSUDx.DxCode1, PtCSUDx.DxCode2, PTCSUDx.DxCode3, PtCSUDx.DxCode4  
FROM
  sos.Patients
  LEFT OUTER JOIN sos.Providers ON Patients.ProviderNum = Providers.ProviderNum
  LEFT OUTER JOIN sos.PtCSU ON Patients.PtNum = PtCSU.PtNum
  LEFT OUTER JOIN sos.PtCSUDx ON PtCSU.PtCSUNum = PtCSUDx.PtCSUNum  
  LEFT OUTER JOIN sos.PtVars ON Patients.PtNum = PtVars.PtNum  
WHERE
  Patients.LicNum = 101
  AND PtCSU.TypeFlag = 'D'  
  AND (SELECT COUNT(*)
            FROM sos.journal
            /* service date range goes on next line */
            WHERE trantype = 'S' AND amount > 0 AND trandate BETWEEN '2000-01-01' AND '2008-06-30') > 0
  /*<-- desired Dx code goes on next line*/
  AND 'F10.150' IN (PtCSUDx.DxCode1, PtCSUDx.DxCode2, PTCSUDx.DxCode3, PtCSUDx.DxCode4)    
ORDER BY
  Providers.ProvCode, Patients.LastName, Patients.FirstName

Count of Patients by Provider with and without Medicaid

Posted on | by SOS Blog | Leave a Comment on Count of Patients by Provider with and without Medicaid

I need to know by provider how many active patients they have and how many have Medicaid coverage.

This query shows the combined use a an IF expression that evaluates based on a subquery that returns a count. In this case, if there is any Medicaid coverage (carriers.coverage = ‘D’) then the expression returns “Yes”, otherwise (no ‘caid coverage) it returns “No”.

SELECT
(a.provfname + ' '+a.provlname) AS "Provider",
(IF (SELECT COUNT(*)
    FROM sos.ptpayors s1 JOIN sos.payors s2 ON s1.payornum = s2.payornum JOIN sos.carriers s3 ON s2.payornum = s3.payornum
    WHERE s1.ptnum = a.ptnum AND s3.coverage = 'D') > 0
THEN 'YES'
ELSE 'NO'
END IF)
AS "Medicaid",
COUNT(DISTINCT a.ptnum) AS "Pt Count"
FROM
sos.rv_charges a
WHERE
a.trandate BETWEEN '2008-01-01' AND '2008-12-31'
GROUP BY
"Provider", "Medicaid"
ORDER BY
"Provider", "Medicaid"