*** 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
