Back to Home

CEM Daily Report

Overview

The purpose of this report is to show which Session Checklists were selected during the audit process. The Script and excel template can be accessed below.

Steps:

1. Check Script and make sure @ReportMonth is for the current month.
2. Execute script in CEM Production server CIWSQLXP0099 (USE Core_CEM)
3. Copy data with headers
4. Copy data with headers in Excel ExcelTemplate
5. Save Excel sheet as 'CEM Daily Checklist - 0410' (0410 is for April 10th, Use current date)
6. Attach Excel sheet to email and send to the following:
   • McCabe Michael
   • Kathy Fairbanks
   • Alexandra Henry
   • Harmony Talsma
   • Pavel Marukovich

Script

declare @ReportMonth varchar(10) = '03/01/2020' --Update month to current month
select distinct Convert(varchar(10),a.ReportMonth, 101) as ReportMonth, f.FullName+'  ['+ f.NTID + ']'  as Advocate,
g.FullName+'  ['+ g.NTID + ']'  as Supervisor,
k.FullName  as Manager, l.FullName  as Director,
h.LookupTitle as AuditType, AccountNo, b.SampleName, c.LookupTitle as LOB,
d.LookupTitle as CallerType,  CONVERT(CHAR(11),a.CallTimeStamp,101) + CONVERT(CHAR( 5),a.CallTimeStamp,114) as CallTimeStamp, a.CallTransactionID as UniqueCallKey,
a.Score,
u.LookUpTitle as ChecklistNote,
left(replace(Replace(replace(Replace(a.Comments,char(13),' '),char(10),' '), char(09), ' '),'"',''''),3999) as CallComments,
e.Fullname as Auditor , DateFirstSubmitted
from smart.Audit_CallRecord a inner join smart.pbc_SampleTable b on a.SampleNo = b.SampleNo
inner join smart.qry_code c on c.LookupNo = b.LineOfBusinessNo and c.LookupCategory ='LineOfBusiness'
left join smart.qry_code d on d.LookupNo = b.CallerTypeNo and d.LookupCategory ='CallerType'
left join smart.sam_SetUpUser e on e.UserNo = a.AuditorNo
left join smart.sam_SetUpUser f on f.UserNo = a.AdvocateNo
left join smart.sam_SetUpUser g on g.UserNo = a.SupervisorNo
left join smart.qry_code h on h.LookupNo = b.SampleGroupNo and h.LookupCategory ='SampleGroup'
left join smart.sam_SetUpUser k on k.UserNo = a.ManagerNo
left join smart.sam_SetUpUser l on l.UserNo = a.directorNo
left join smart.[Audit_ErrorCharged] m on m.UniqueCallNo = a.UniqueCallNo and m.RemovalReasonNo = 0 and m.Deleted = 0
left join smart.MasterErrorCode n on n.ErrorCode = m.ErrorCode
left join smart.qry_code o on o.LookupNo = n.tnpsno and o.LookupCategory ='tnps'
left join smart.qry_code p on p.LookupNo = n.CategoryGroupNo and p.LookupCategory ='CategoryGrouping'
left join smart.qry_code q on q.LookupNo = n.BusinessOwnerNo and q.LookupCategory ='BusinessOwner'
left join [smart].[SOPList] r on r.UniqueNo = m.SOPNo
left join smart.qry_code s on s.LookupNo = b.CallCenterNo and s.LookupCategory ='Office'
left join [smart].[sam_SessionCheckList] t on t.UniqueCallNo = a.UniqueCallNo
left join smart.qry_code u on u.LookupNo = t.CheckListNo and u.LookupCategory ='SessionCheckList'
where a.StageNo = 20 and
ReportMonth =@ReportMonth and a.Deleted = 0 and u.LookUpTitle is not null