Category: G4 Queries

We need some basic demographics, along with primary provider and number of non-zero visits during a specified date range.

This is a pretty basic query, but it does include a subquery to provide the number of visits.

SELECT 
  a.lastname+', '+a.firstname+' '+a.id AS "Patient Name/ID",
  COALESCE(b.provcode,'') AS "Provider",
  (SELECT COUNT(*) 
   FROM sos.journal jou JOIN sos.jcharges chg 
   WHERE jou.ptnum = a.ptnum 
     AND jou.trandate BETWEEN '2000-01-01' AND '2016-12-31'
     AND jou.amount > 0) AS "# of Visits",
  sos.AGEINYEARS(a.DOB,TODAY()) AS "Age",
  a.sex AS "Gender" 
FROM
  sos.patients a 
  LEFT OUTER JOIN sos.providers b ON a.providernum = b.providernum
  WHERE "# of Visits" > 0
ORDER BY 
  "Patient Name/ID"

All we need is patient AR (same as currently reported) broken down by service code within each patient’s balance.  As an example, if patient X has $18,000 of RTC gross charges and $10,000 of PHP gross charges I want to see those two amounts broken out (separate line items) that then roll up into total AR of $28,000. 

We can use a GROUP BY with the ROLLUP option to produce a query that provides exactly what you need. The hard lifting is done by an existing database view that calculates the aging. The query just picks the needed aging values from that view and groups them by the desired patient and service code values. Remember that you read “NULL” in a ROLLUP query as “All”. When you see a line that starts with a patient name, and the next column (Service Code) reads “NULL”, the aging values that follow represent the totals for all service codes. Note that this total line will precede the breakdown by service for each patient.

SELECT
(h.lastname+', '+h.firstname+' / '+h.id) AS "patient",
f.srvcode AS "service code",
SUM(a.bal) AS "totbalance",
SUM(a."current") AS "currentbal",
SUM(a."31-60 days") AS "31to60",
SUM(a."61-90 days") AS "61to90",
SUM(a."91-120 days") AS "91to120",
SUM(a."121-150 days") AS "121to150",
SUM(a."151-180 days") AS "151to180",
SUM(a."over 180") AS "Over180"
FROM
sos.v_chsaging a
JOIN sos.jcharges b on a.jnum = b.jnum
JOIN sos.journal c on b.jnum = c.jnum
JOIN sos.providers e on b.providernum = e.providernum
JOIN sos.services f on b.servicenum = f.servicenum
LEFT OUTER JOIN sos.lookups g on c.sortcode = g.lunum
JOIN sos.patients h ON c.ptnum = h.ptnum
JOIN sos.ptpayors i ON a.ptpayornum = i.ptpayornum
JOIN sos.payors j ON i.payornum = j.payornum
WHERE
a.chgsplbal > 0
GROUP BY ROLLUP ("patient","service code")
ORDER BY "patient","service code"

*** Note: It is not possible to delete patients from within SOS unless there are no significant records (such as financial transactions or progress notes) associated with them. Contact support for information and recommended procedures for backing up and purging old accounts.

A data breach involving patient information can be very costly to remediate. As a result an organization may want to identify and remove patient records from the database as soon as the law allows (often seven years after the last service was rendered).

The following query identifies accounts that meet the following conditions:
1. If the patient was 17 or younger when treatment ended, then records must be retained until 7 years after the patient turned 18.
2. If the patient was 18 or older when treatment ended, then records must be retained at least 7 years after treatment ended.

Adjust the formula values if the record retention period is more than 7 years in your jurisdiction and/or the age of majority is not 18. Also note that “last date of service” is defined here as the last date of service where a fee was charged (even if 100% adjusted off). The query will not work if the last service rendered was entered with a fee of $0.

SELECT 
  b.Lastname AS "Last Name",
  b.Firstname AS "First Name",
  b.ID,
  b.ptnum, 
  b.dob AS "Date of Birth",
  DATE(DATEADD(year,18,b.dob)) AS "18th Birthday",
  a.lfeedate AS "Last Date of Service",
  DATE (CASE WHEN "Last Date of Service" < "18th Birthday" 
        THEN DATEADD(year,7,"18th Birthday") 
        ELSE DATEADD(year,7,"Last Date of Service") 
        END CASE) AS "Keep Records Until" 
FROM 
  sos.ptvars a 
  JOIN sos.patients b ON a.ptnum=b.ptnum 
WHERE 
  b.flag BETWEEN 0 and 1 AND 
  b.dob IS NOT NULL AND 
  "Last Date of Service" IS NOT NULL 
  AND licnum > 100
ORDER BY "Last Name", "First Name", b.ID

SELECT
  lastname  AS "Last Name",
  firstname AS "First Name",
  id,
  intakedate,
  dischargedate
FROM
  sos.patients
WHERE
  sos.AGEINYEARS(dob,intakedate) < 18 AND sos.AGEINYEARS(dob,COALESCE(dischargedate,today())) > 17
ORDER BY 
  lastname,firstname, id