/*What notifications have been triggered by a user*/ Select pi.name as program_name,p.patient_id,t.message_type ,c.notification_type, t.recipient_type,t.status,t.created_at,t.triggered_aggregation_type,t.rule_metric_type,t.triggered_value,t.rule_comparator,t.rule_threshold,t.rule_metric_unit 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 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) /*Below connects user_program_id to user through notification_config*/ Inner Join notification_configuration as c on c.user_program_id = p.user_program_id Inner Join notification_tracking as t on t.patient_id = c.patient_id Inner Join patient_program as pp on pp.user_program_id = p.user_program_id Inner Join provider_info as pr on pr.provider_id = pp.reviewing_provider_id /*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) /*Update the patient_id below to pull for one user*/ and p.patient_id = '3823abdda4db13203c999fb322ad5854' Order by t.created_at DESC
General
Content
Integrations