Table of Contents

Purpose

...

• day (last 1 day)
• month (last 30 daysmonth)
• total 

Successful/unsuccessful SMS submissions stats

Fetch records from VERIFICATION.sms_logs, where:

• status not in  ('Accepted', 'Enroute')

• now()-inserted_at<=inputed_period (if inputed_period = total then all sms)
• if body<>type<>'VERIFICATION' then 'Medication request' else bodytype

Calculate successful ratio:

• count(status='Delivered')/count(status<>'Delivered'*)

Undelivered SMS

Fetch records from VERIFICATION.sms_logs, where:

• status not in  ('Accepted', 'Enroute')

• now()-inserted_at<=inputed_period (if inputed_period = total then all sms)
• if body<>type<>'VERIFICATION' then 'Medication request' else bodytype

Calculate Undelivered SMS qty:

• count(status<>'Delivered')

SMS delivery latency

Fetch records from VERIFICATION.sms_logs, where:

• status = 'Delivered'

• now()-inserted_at<=inputed_period (if inputed_period = total then all sms)
• if body<>type<>'VERIFICATION' then 'Medication request' else bodytype

Calculate SMS delivery latency based on groups:

• define groups:
  • '<=1 min':status_changed_at - inserted_at<=1 min 
  • '1 - 5 min':  status_changed_at - inserted_at<=5 min 
  • '5 - 30 min': status_changed_at - inserted_at<=30 min 
  • '30 min - 12 hours': status_changed_at - inserted_at <=30 min - 12 hours 
  • '>12 hours': status_changed_at - inserted_at> 12 hours
• count(*) - sms qty in each group

...