Hello,
The final query would depend a lot on how your environment is set, how is the expected call flow, and things like that.
But, as an example, try this at GIM database (formated for MS SQL Server DBMS):
[code]
select
irf.interaction_id,
irf.interaction_resource_ordinal,
it.interaction_type,
dateadd(hour,cast(dt.label_tz as int), dateadd(second, irf.start_ts, '1970-01-01')) as start_time,
dateadd(hour,cast(dt.label_tz as int), dateadd(second, irf.end_ts, '1970-01-01')) as end_time,
rp.resource_name as routing_point,
vq.resource_name as virtual_queue,
i.source_address as caller_number,
i.target_addres as called_number,
td.result_reason
from
interaction_resource_fact irf
inner join date_time dt on dt.date_time_key = irf.start_date_time_key
inner join media_type mt on mt.media_type_key = irf.media_type_key
inner join interaction_type it on it.interaction_type_key = irf.interaction_type_key
inner join interaction_fact i on i.interaction_id = irf.interaction_id and i.start_date_time_key = irf.interaction_sdt_key
inner join technical_descriptor td on td.technical_descriptor_key = irf.technical_descriptor_key
inner join resource_ rp on rp.resource_key = irf.last_rp_resource_key
left outer join mediation_segment_fact msf on msf.ixn_resource_id = irf.interaction_resource_id
left outer join resource_ vq on vq.resource_key = msf.resource_key
where
mt.media_name_code = 'VOICE'
and td.technical_result_code = 'CUSTOMERABANDONED'
and dt.label_yyyy_mm_dd = '2018-12-12' -- Filter by date here if you want to
[/code]
