Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Select v.sr_name, v.workdate, u.Calls_in, u.Calls_Out, u.Chats, u.reaches, u.books, u.attends, v.hoursworked
- from
- (
- select sr_name,
- cast(punchintime as date) as workdate,
- ROUND(sum(cast(datediff(minute,punchintime, punchouttime) as real)/60),2) as hoursworked,
- count(*) as punches
- from
- (
- select sr_name,
- punchintime = punchdatetime,
- punchouttime = ISNULL((select top 1 pc2.punchdatetime from punchclock pc2 where pc2.punchdatetime > pc.punchdatetime and pc.servrepid = pc2.servrepid and pc2.inout = 0 order by pc2.punchdatetime), getdate())
- from punchclock pc
- join servicereps sr on pc.servrepid = sr.servrepid
- where punchyear >= 2017 and pc.inout = 1
- )x
- group by sr_name, cast(punchintime as date)
- ) v
- join
- (
- select sr.sr_name, t.*,
- Calls_Out = (select count(*) from CRM_Correspondence cr where cast(cr.DateCreated as date) = workdate and StatusType IN ('Follow Up','Call Attempted','Call Reached','Returned Call') and cr.ServRepID = t.servrepid),
- Calls_In = (Select count(*) AS numCallIns
- from CRM_Accounts ca
- JOIN Contact c on c.contactid = ca.contactid
- JOIN CRM_Correspondence cor on cor.CorrespondenceID = ca.firstCorRecordID
- JOIN referraltypes rt on c.referralid = rt.referralid
- where cast(cor.DateCreated as date) = workdate and rt.ReferralName LIKE '%Call In%'
- and cor.servrepid = t.servrepid),
- Chats = (select COUNT(*)
- from crm_correspondence cor
- left join Contact ct on cor.ContactID = ct.contactID
- left join ReferralTypes rt on rt.ReferralID = ct.ReferralID
- where cast(cor.datecreated as date) = workdate
- and ((cor.StatusType = 'Chat' and cor.chanid in (3,4)) or (cor.StatusType = 'NEW' and rt.ReferralName like '%Chat%'))
- and cor.servrepid = t.servrepid),
- reaches = (select count(*) from CRM_Correspondence cr where cast(cr.DateCreated as date) = workdate and (StatusType = 'call reached' or StatusType like '%SCHEDULE%') and cr.ServRepID = t.servrepid),
- books = (select count(*) from os_appointments o where cast(o.DateCreated as date) = workdate and isnull(o.confirmedby, o.booked_by) = t.servrepid),
- attends = (select count(*) from os_appointments o where cast(o.DateCreated as date) = workdate and isnull(o.confirmedby, o.booked_by) = t.servrepid and o.appointmentStatus = 'attended')
- from
- (
- select cast(cor.datecreated as date) workdate, cor.ServRepID, correspondenceID
- from CRM_Correspondence cor
- where cor.datecreated >= '2017-01-01'
- group by cast(cor.datecreated as date), cor.servrepid, correspondenceID
- ) t
- join ServiceReps sr on t.ServRepID = sr.ServRepID
- join CRM_Accounts ca on t.CorrespondenceID = ca.firstCorRecordID
- ) u on v.sr_name = u.sr_name and v.workdate = u.workdate
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement