Enhanced abandoned call Reports & Dashboards
- Taupheeq Omar (Unlicensed)
Report – Report Export attached
Â
Â
Dashboard:
Box – Unreturned calls per group for today
Box Custom Query:
Unreturned – Highlighted ids will need to be updated based on group ID
select Count (distinct tblLog.logID) from tblLog
LEFT JOIN tblDashboardUnansweredCallsReponseLinks AS unansweredReponseLinks ON tblLog.logID = unansweredReponseLinks.OriginalCallLogId
LEFT JOIN tblDirAcd AS dirAcdViaSecParty ON tblLog.party2number = CAST( dirAcdViaSecParty.num AS Varchar(MAX) )
AND tblLog.party2id = 3
LEFT JOIN tblACDAgentEvents AS acdagentevents ON tblLog.logid = acdagentevents.logid
LEFT JOIN tblDirAcd AS dirAcdAcdEvents ON dirAcdAcdEvents.num = acdagentevents.ACDGroup and tblLog.siteid = dirAcdAcdEvents.site
LEFT JOIN tblCallFlagsEx AS callflags ON tblLog.logID = callflags.logId where ( tblLog.typeID = 3 ) and ( (EXISTS (
select
l.logID
from tblLog as l
LEFT JOIN tblCTICallSegments AS segments ON l.logID = segments.logID
LEFT JOIN tblDirACD AS dirACDAcd ON segments.ACDGroup = dirACDAcd.num
Where l.logid = tblLog.logid and dirACDAcd.ID in (12)
) ) or ( dirAcdViaSecParty.ID in (12) ) or (dirAcdAcdEvents.ID in (12) ) ) and ( Unanswered = 1 )
and (tblLog.fdate >= CAST(GETDATE() as Date))
and unansweredReponseLinks.CallTypeOfResponse is Null
Â
Called Back – Highlighted ids will need to be updated based on group ID
select Count (distinct tblLog.logID) from tblLog
LEFT JOIN tblDashboardUnansweredCallsReponseLinks AS unansweredReponseLinks ON tblLog.logID = unansweredReponseLinks.OriginalCallLogId
LEFT JOIN tblDirAcd AS dirAcdViaSecParty ON tblLog.party2number = CAST( dirAcdViaSecParty.num AS Varchar(MAX) )
AND tblLog.party2id = 3
LEFT JOIN tblACDAgentEvents AS acdagentevents ON tblLog.logid = acdagentevents.logid
LEFT JOIN tblDirAcd AS dirAcdAcdEvents ON dirAcdAcdEvents.num = acdagentevents.ACDGroup and tblLog.siteid = dirAcdAcdEvents.site
LEFT JOIN tblCallFlagsEx AS callflags ON tblLog.logID = callflags.logId where ( tblLog.typeID = 3 ) and ( (EXISTS (
select
l.logID
from tblLog as l
LEFT JOIN tblCTICallSegments AS segments ON l.logID = segments.logID
LEFT JOIN tblDirACD AS dirACDAcd ON segments.ACDGroup = dirACDAcd.num
Where l.logid = tblLog.logid and dirACDAcd.ID in (12)
) ) or ( dirAcdViaSecParty.ID in (12) ) or (dirAcdAcdEvents.ID in (12) ) ) and ( Unanswered = 1 )
and (tblLog.fdate >= CAST(GETDATE() as Date))
and unansweredReponseLinks.CallTypeOfResponse = 2
Â
Returned – Highlighted ids will need to be updated based on group ID
select Count (distinct tblLog.logID) from tblLog
LEFT JOIN tblDashboardUnansweredCallsReponseLinks AS unansweredReponseLinks ON tblLog.logID = unansweredReponseLinks.OriginalCallLogId
LEFT JOIN tblDirAcd AS dirAcdViaSecParty ON tblLog.party2number = CAST( dirAcdViaSecParty.num AS Varchar(MAX) )
AND tblLog.party2id = 3
LEFT JOIN tblACDAgentEvents AS acdagentevents ON tblLog.logid = acdagentevents.logid
LEFT JOIN tblDirAcd AS dirAcdAcdEvents ON dirAcdAcdEvents.num = acdagentevents.ACDGroup and tblLog.siteid = dirAcdAcdEvents.site
LEFT JOIN tblCallFlagsEx AS callflags ON tblLog.logID = callflags.logId where ( tblLog.typeID = 3 ) and ( (EXISTS (
select
l.logID
from tblLog as l
LEFT JOIN tblCTICallSegments AS segments ON l.logID = segments.logID
LEFT JOIN tblDirACD AS dirACDAcd ON segments.ACDGroup = dirACDAcd.num
Where l.logid = tblLog.logid and dirACDAcd.ID in (12)
) ) or ( dirAcdViaSecParty.ID in (12) ) or (dirAcdAcdEvents.ID in (12) ) ) and ( Unanswered = 1 )
and (tblLog.fdate >= CAST(GETDATE() as Date))
and unansweredReponseLinks.CallTypeOfResponse =3