Category: G4 Queries

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

Patients with Scheduler and Transaction Screen Warnings

Posted on | by SOS Blog | Leave a Comment on Patients with Scheduler and Transaction Screen Warnings

As we move into next year, we must prepare for our usual mad flurry of activity in updating patient records to reflect the new calendar year. Deductibles need to be re-configured. Copays that increase with session frequency also need to be readjusted. We usually put such information under the ADDITIONAL tab in the patient record, specifically in the spaces called Scheduler reminder and Transaction Screen Reminder. I need a query that shows the data entered in these fields for each patient.

This query will output a line if there is either a “don’t schedule” reason OR a transaction memo. The list will include only Active list patients with no discharge date entered and something entered in either or both of the reminder fields.

SELECT
  lastName+','+firstName+' '+ id AS "Patient",
  noschedreason,
  trannote
FROM
  sos.patients
WHERE
  flag = 0
  AND dischargedate IS NULL
  AND (noSchedReason <>''  OR trannote <>'')
ORDER By LastName, FirstName