The following table lists the ways we can classify a patient for analytics and reporting purposes. Not all definitions are currently used, but they are included for completeness and to provide a better context for the ones that are used.
Note
When selecting data based on the following definitions, follow the guidelines in the “Fetching data Using the Partition Dates” section of - Analytics Reporting Partition Generation AlgorithmApproved
Patient Type |
Description |
SQL criteria for Identifying patients 1 |
|
---|---|---|---|
1 |
Reporting Patient |
A PainChek patient that is:
WarningPainChek reports should only ever include “Reporting Patients” - other PainChek patients are always excluded. |
deleted_at is null AND training_record = FALSE created_at < group_end |
2 |
Assessed Patient |
A Reporting Patient that has at least one assessment in the reporting period. |
is [Reporting Patient] AND EXISTS ( SELECT * FROM assessment WHERE assessment.patient_id = patient.uuid AND assessment_date BETWEEN group_start AND group_end) |
3 |
Active Patient |
A Reporting Patient that is currently active or was active for at least some of the period (even if it’s just a few days). |
is [Reporting Patient] AND (archived_at IS NULL OR archived_at > group_start) |
4 |
Currently Active Patient |
A Reporting Patient that was active at the end of the period |
is [Reporting Patient] AND (archived_at IS NULL OR archived_at > now) |
5 |
Archived Patient |
A Reporting Patient that was archived prior to the start of the period. Active Patients + Archived Patients = Reporting Patients |
is [Reporting Patient] AND archived_at < group_start |
6 |
Created in Period Patient |
A Reporting Patient that was created within the reporting group |
is [Reporting Patient] AND created_at BETWEEN group_start AND group_end |
7 |
Archived in Period Patient |
A Reporting Patient that was archived within the reporting group. Note that a patient can be both “Created in Period” and “Archived in Period” (for the same reporting group) |
is [Reporting Patient] AND archived_at BETWEEN group_start AND group_end |
8 |
Created in Prior Period Patient |
A Reporting Patient that was created before the start of the reporting group. Created in Period Patients + Created in Prior Period Patients = Reporting Patients |
is [Reporting Patient] AND created_at < group_start |
9 |
Archived in Prior Period Patient |
An Inactive Patient that was archived before the start of the reporting group. Note this is the same definition as Archived Patient |
is [Reporting Patient] AND archived_at < group_start |
Note
-
The filter is assumed to apply patient_patient records unless a table name is explicitly stated. “patient” is used as an alias for the patient_patient table and “assessment” is used as an alias for the assessment_assessment table
-
When a patient type label refers to period e.g. ‘Active at End of Period Patient’, period refers to the interval of time we are reporting over i.e. group_start to group_end, not the partition type called ‘period’