Re: Reminders Statistic Report
Message from benjamin.charlton@kalingaparkvetsurgery.com.au benjamin.charlton@kalingaparkvetsurgery.com.au
The reminder report is pretty easy
Each reminder can store the following
1. When it was created.
2. How many have been sent (it increments when you do a reminder run that
reminder is valid for)
3. Whether it was "COMPLETED" or "CANCELLED". and the DATE it was COMPLETED.
4. When it was DUE
The dates you enter are based on REMINDER DUE DATE
A reminder is marked as a +ve response if it is completed.
A reminder is marked as -ve response if it is cancelled or not completed.
If a person responds before you you sent any it is marked as responde with 0
reminders sent.
I assume that a no reponse count at 0 indicates a cancelled reminder without
it being sent.
The percentage is the number of positive response / total sent.
Hence in looking at the report it isnt reporting on response based on number
actual reminders sent more so response based on the reminders u have in the
system.
Example below. sorry about the formatting.
Reminder Type No reminder Sent 1 reminder Sent 2
reminders Sent 3+ reminders Sent Total No
Resp. Response % No Resp. Response
% No Resp. Response % No Resp.
Response % No Resp. Response Total %
Canine C3 Vacc 0 1 50.00% 0 0 0.00% 0 1 50.00% 0 0
0.00% 0 2 2 100 Canine C5 Vacc 7 5 2.60% 4 67
35.10% 12 38 19.90% 41 17 8.90% 64 127 191 66.50%
Hence I had 191 C5 vaccinations due in the period I selected. Of these 7
were cancelled before I sent any reminders. 5 were done before I sent
reminders. After I sent the first reminder for each vaccination I got 67
responses. 4 cancelled. After I sent a 2nd reminder a further 38
responded. 12 Cancelled. After the 3rd reminder (in our case we call
them) 17 responded and 41 didnt respond (thats the last reminder we send so
in this case it includes NO response and cancelled).
In total for my 191 reminders in that period that were due I got 66% back
thru the door for a repeat visit...probably needs work!
SQl for the report is below.
select reminderType, sum(if(reminderCount= 0 and (reminderStatus =
"IN_PROGRESS" or reminderStatus = "CANCELLED"),1,0)) as rem0Incomplete,
sum(if(reminderCount= 0 and reminderStatus = "COMPLETED",1,0)) as
rem0Complete, sum(if(reminderCount= 1 and (reminderStatus = "IN_PROGRESS" or
reminderStatus = "CANCELLED"),1,0)) as rem1Incomplete, sum(if(reminderCount=
1 and reminderStatus = "COMPLETED",1,0)) as rem1Complete,
sum(if(reminderCount= 2 and (reminderStatus = "IN_PROGRESS" or reminderStatus
= "CANCELLED"),1,0)) as rem2Incomplete, sum(if(reminderCount= 2 and
reminderStatus = "COMPLETED",1,0)) as rem2Complete, sum(if(reminderCount>= 3
and (reminderStatus = "IN_PROGRESS" or reminderStatus = "CANCELLED"),1,0)) as
rem3Incomplete, sum(if(reminderCount>= 3 and reminderStatus =
"COMPLETED",1,0)) as rem3Complete, sum(if(reminderStatus = "IN_PROGRESS" or
reminderStatus = "CANCELLED",1,0)) as remTotalIncomplete,
sum(if(reminderStatus = "COMPLETED",1,0)) as remTotalComplete from ( select
rt.name as reminderType, a.activity_end_time as dueDate, a.status as
reminderStatus, ad1.value as reminderCount, ad2.value as reminderCompleted
from acts a left outer join act_details ad1 on ad1.act_id = a.act_id and
ad1.name = "reminderCount" left outer join act_details ad2 on ad2.act_id =
a.act_id and ad2.name = "completedDate" inner join participations prt on
prt.act_id = a.act_id and prt.arch_short_name = "participation.reminderType"
inner join entities rt on rt.entity_id = prt.entity_id where
a.arch_short_name = "act.patientReminder" and rt.name like $P{Reminder Type}
and date(a.activity_end_time) between $P{Reminder Due Start} and $P{Reminder
Due End}) as reminders group by reminderType
SQL is above
_______________________________________________
OpenVPMS User Mailing List
users@lists.openvpms.org
To unsubscribe or change your subscription visit:
http://lists.openvpms.org/listinfo/users
Posts from this mailing list can be viewed online and replied to in the OpenVPMS User's forum- http://tinyurl.com/openvfu
