Hi cdtj,
I was working on the same thing using sql. And this is what I came up with... Hopefully it might help if you have not already found a solution
select
ticket_id
,mapped_cr
,srv_desc.sym as [Service Type]
,srv_desc.Description
,slatpl.sym as [Service Type Event]
,case
when status_flag = 0 then 'Cancelled'
when status_flag = 1 then 'Complete'
when status_flag = 2 then 'Pending'
when status_flag = 3 then 'Repeating'
when status_flag = 1 then 'Complete'
when status_flag = 12 then 'Delayed'
when status_flag = 13 then 'Deplayed Repeating'
end as Status
,case
when sla_viol_status = 0 then 'Not Violated'
when sla_viol_status =1 then 'Violated'
end as [Violation Status]
,convert(varchar(20), dateadd (ss, last_ttv_upd -21600, '1/1/1970') , 101) + ' ' + + LTRIM(RIGHT(CONVERT(CHAR(20), dateadd (ss, last_ttv_upd -21600, '1/1/1970'), 22), 11)) as [Evaluated On]
,convert(varchar(20), dateadd (ss, start_time -21600, '1/1/1970') , 101) + ' ' + + LTRIM(RIGHT(CONVERT(CHAR(20), dateadd (ss, start_time -21600, '1/1/1970'), 22), 11)) as [SLA Start Time]
,case
when status_flag = 12 or status_flag = 13 then null
else convert(varchar(20), dateadd (ss, fire_time -21600, '1/1/1970') , 101) + ' ' + + LTRIM(RIGHT(CONVERT(CHAR(20), dateadd (ss, fire_time -21600, '1/1/1970'), 22), 11))
end as [Expiration Time]
,case
when status_flag = 12 or status_flag = 13 then
CAST((fire_time/ 86400) AS varchar(10)) + ' d ' + CAST((fire_time % 86400 / 3600) AS varchar(10)) + ' h ' + CAST(((fire_time % 86400) % 3600 / 60) AS varchar(10)) + ' m'
else null
end as [SLA Remaining]
,num_fire
-- ,*
from attached_sla sla -- ticket's attached service type
join srv_desc ON srv_desc.persid = sla.map_sdsc -- service type' description table
join -- links service type's descriptions (srv_desc) with SLA's attached events (att_evt) and filters for violations only
( select sym, service_type, event from slatpl where object_type = 'cr' and sym like '%Violation'
) slatpl on slatpl.service_type = sla.map_sdsc
join att_evt ae on ae.event_tmpl = slatpl.event and ae.obj_id = sla.mapped_cr -- service type sla events
where mapped_cr in
(
select persid from call_req where sla_violation > 0
)