Some of our customers have used this query as a means of trying to gauge what the member’s buy-in on taking readings has been. This data has been compared to the user readings to see how much the engagement has benefited the user’s improved health. Unfortunately, the user’s actual reading data can’t be accessed through the ETL extract, so pulling the actual reading data isn’t something offered through this reporting solution.
Query is written in PostgreSQL
/*Pulls reading count on currently enrolled for this month and previous
Only as current as the day your last export came to you...*/
Select h.patient_id,pi.name as program_name,p.latest_upload_time,h.status,d.current_month_days_with_reading,d.current_month_total_readings,d.previous_month_days_with_reading,d.previous_month_total_readings
From patient_program_history as h
/*To get latest upload time */
Inner Join patient_info as p on p.user_program_id = h.user_program_id
/*To get program name */
Inner Join program_info as pi on pi.program_id = h.program_id
/*To get reading counts*/
Inner Join device_usage as d on d.patient_id = h.patient_id
/*To isolate last status update timestamp for users_program_id*/
Inner Join (Select user_program_id,max(updated_at) as updated_at
From patient_program_history as h
Group by user_program_id
) as max on (max.user_program_id = h.user_program_id AND max.updated_at = h.updated_at)
/*To exclude user_program_id that isn't active or on the way to active*/
Where h.user_program_id not in (Select user_program_id
From patient_program_history as h
Where h.status in ('Unenrolled','Archived','Abandoned')
Group by user_program_id)
/*Isolate users that have synced data previously*/
and status = 'Initial Data Uploaded'
/*below gives for one program, comment out if you want all programs*/
and pi.name = '<Program_Name>'
/*edit below with patient_id if you are searching one user, or comment out of you are searching all users*/
and h.patient_id = '<Patient_id>'
Group by h.patient_id,pi.name ,p.latest_upload_time,h.status,d.current_month_days_with_reading,d.current_month_total_readings,d.previous_month_days_with_reading,d.previous_month_total_readings;