Announcement

Collapse
No announcement yet.
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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.

    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
    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.

    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()
    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

    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()
    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.

    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]
    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.

  • #2
    I'm interested in trying your queries. What tool are you using to accomplish this? I tried creating the view in MySQL Administrator, but I'm getting an error saying " CREATE VIEW command denied to user 'eccro'@'mycomputername' for table 'main' " I'm not a DBA, but my guess is this is a permissions issue. How would I resolve this and do I need to create a table 'main' (assuming this is the View name)?

    Comment

    Working...
    X