Tweet
Fun with Cradle to Grave Reporting
I did an install recently where I got to play with ECC's c2g reporting, and I thought I would share some of the SQL queries I came up with.
I made this a view to make it a bit easier to run other queries against the event table. It makes the logs human readable by pulling the entity names from the other tables as opposed to using the ids.
Shows all of the events for today based on ANI. You could also replace the last AND with a clause to do it between specific dates
Shows all of the calls the agent participated in by BCC id. You could also replace the last AND with a clause to do it between specific dates. This BCC id could then be ran against the view above to list out all the interactions for that call.
Shows all of the call profile data for a specific event.
I need to get around to putting this all into Crystal Reports to try and create a cleaner interface, but I figured this was a start.
Code:
select e.g_event_id,e.g_cid,e.event_time,e.prim_cid,t.event_name, i.irn_dn,c.ccs_name,s.srv_name,r.rc_name,wr.w_name,iva.ivr_app_name, d.dn_name,di.dl_name,cc.cause_name from events e inner join event_types t on e.event_id = t.event_id left outer join irn i on i.irn_id = e.irn_id left outer join ccs_hdr c on e.ccs_id = c.ccs_id left outer join services s on s.srv_id = e.srv_id left outer join release_codes r on r.rc_id = e.release_id left outer join wu_code wr on e.w_number = wr.w_number left outer join ivr_apps iva on e.ivr_app_id = iva.ivr_app_id left outer join dnis d on e.irn_id = d.dnis_id left outer join dial_lists di on e.dl_id = di.dl_id left outer join cause_code cc on e.cause_id = cc.cause_id
Code:
select distinct e.g_cid from event_parties p join party_types pt on p.party_type = pt.party_type join events e on p.g_event_id = e.g_event_id where p.ani = '[I]phone number[/I]' and e.event_time > curdate()
Code:
select distinct e.g_cid from event_parties p join party_types pt on p.party_type = pt.party_type join events e on p.g_event_id = e.g_event_id where p.device = '[I]agent extension[/I]' and e.event_time > curdate()
Code:
select ecp.g_event_id,cp.field_name,ecp.field_value from event_call_profile ecp join cp_fields cp on ecp.field_id = cp.field_id and ecp.field_type = cp.field_type where ecp.g_event_id = [I]event id[/I]
I need to get around to putting this all into Crystal Reports to try and create a cleaner interface, but I figured this was a start.
Comment