Minutes clinicians spent on Patient per Month

In Impact Clinicians can add minutes spent on notes on patients. Some customers needed the means to pull those numbers for billing purposes.

Query is written in PostgreSQL

/*Below will pull the sum of hours spent per user per month. Limited to currently enrolled users*/

Select h.patient_id,p.validic_id,p.phone_number
/*Uncomment below if you want the category included. Take same action in Group By clause*/
--,cn.category
,sum(cn.time_spent_minutes::numeric) as minutes_spent,to_char(cn.created_at, 'YYYY-MM') As Year_month
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 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 maxh on (maxh.user_program_id = h.user_program_id AND maxh.updated_at = h.updated_at)
Join clinician_notes as cn on cn.patient_id = p.patient_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)
/*edit below with patient_id and uncomment if searching on one patient*/
--and h.patient_id = '3823abdda4db13203c999fb322ad5854'
Group by h.patient_id,p.validic_id,p.phone_number,to_char(cn.created_at, 'YYYY-MM')
/*If you add category in select statement uncomment below*/
--,cn.category
Having sum(cn.time_spent_minutes::numeric) != 0
Order by to_char(cn.created_at, 'YYYY-MM') desc;