Table of Contents

Purpose

...

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

Successful/unsuccessful SMS submissions stats

Fetch records from VERIFICATION.sms_logs, where:

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

...

Calculate successful ratio:

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

Undelivered SMS

Fetch records from VERIFICATION.sms_logs, where:

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

...

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<>'VERIFICATION' then 'Medication request' else body

Calculate SMS delivery latency based on groups:

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

...