Author: SOS Blog

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 Service and Sort Code for Period

Posted on | by SOS Blog | Leave a Comment on Payments by Service and Sort Code for Period

I need a break down of payments totalled by Service Code and Sort Code. We use the SortCode field on the Charge entry to indicate the facility where the services were rendered.

SELECT
  srvcode, COALESCE(lucode,'None') AS "Facility", sum(crsplamt) AS Payments
FROM
  sos.rv_creditsplits a LEFT OUTER JOIN sos.lookups b ON a.srv_sortcode = b.lunum
WHERE
  CredType <> 'Adjustment'
AND
  Srv_Date BETWEEN '2008-01-01' AND '2008-12-31'
GROUP BY
  srvcode, "Facility"
ORDER BY
  srvcode, "Facility"

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 Specified Diagnosis, Intake Date, And Last Date of Service

Posted on | by SOS Blog | Leave a Comment on Patients With Specified Diagnosis, Intake Date, And Last Date of Service

I want to see a list of clients bearing a certain diagnosis, along with their intake date, each of their four diagnoses, and last date of service.

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
  PtCSUDx.DxCode1 = '300.14'   /*<-- desired Dx code goes here*/
ORDER BY
  Providers.ProvCode, Patients.LastName

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