We're using the ZD API to replicate our Support data to our own data warehouse.
I'm needing help figuring out how to calculate the SLA from the native ZD tables:
tickets
ticket_metrics?
ticket_metric_events?
The SQL I have below, is not coming close to matching what ZD calculates in Explore.
Anyone succesfully done this?
Below are the queries I've written to get the following:
1. Tickets under SLA
2. Tickets w/SLA breach
1. SQL for Tickets under SLA
select distinct tme.ticket_id from ticket_metric_events tme
where tme.type = 'apply_sla'
and (tme.metric = 'reply_time' or tme.metric = 'pausable_update_time')
and tme.sla__policy__id in (9999999, 9999999999)
and tme.sla__business_hours = 'True'
2. SQL for Tickets w/SLA breach
select distinct t.id from tickets t
where t.idin
(
select distinct tme2.ticket_id from ticket_metric_events tme2
where tme2.type= 'breach'
)
and t.idin
(
select distinct tme.ticket_id from ticket_metric_events tme
where tme.type = 'apply_sla'
and (tme.metric = 'reply_time' or tme.metric = 'pausable_update_time')
and tme.sla__policy__id in (9999999, 9999999999)
and tme.sla__business_hours = 'True'
)