SHARE
TWEET

Untitled

a guest Jul 17th, 2019 66 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Select v.sr_name, v.workdate, u.Calls_in, u.Calls_Out, u.Chats, u.reaches, u.books, u.attends, v.hoursworked
  2. from
  3. (
  4. select sr_name,
  5. cast(punchintime as date) as workdate,
  6. ROUND(sum(cast(datediff(minute,punchintime, punchouttime) as real)/60),2) as hoursworked,
  7. count(*) as punches
  8. from
  9. (
  10. select sr_name,
  11. punchintime = punchdatetime,
  12. 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())
  13.  
  14. from punchclock pc
  15. join servicereps sr on pc.servrepid = sr.servrepid
  16. where punchyear >= 2017 and pc.inout = 1
  17. )x
  18. group by sr_name, cast(punchintime as date)
  19. ) v
  20.  
  21. join
  22. (
  23. select sr.sr_name, t.*,
  24. 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),
  25. Calls_In =  (Select count(*) AS numCallIns
  26.         from CRM_Accounts ca
  27.             JOIN Contact c on c.contactid = ca.contactid
  28.             JOIN CRM_Correspondence cor on cor.CorrespondenceID = ca.firstCorRecordID
  29.             JOIN referraltypes rt on c.referralid = rt.referralid
  30.         where cast(cor.DateCreated as date) = workdate and rt.ReferralName LIKE '%Call In%'
  31.         and cor.servrepid =  t.servrepid),
  32. Chats = (select COUNT(*)
  33.             from crm_correspondence cor
  34.             left join Contact ct on cor.ContactID = ct.contactID
  35.             left join ReferralTypes rt on rt.ReferralID = ct.ReferralID
  36.             where cast(cor.datecreated as date) = workdate
  37.             and ((cor.StatusType = 'Chat'  and cor.chanid in (3,4)) or (cor.StatusType = 'NEW' and rt.ReferralName like '%Chat%'))
  38.             and cor.servrepid =  t.servrepid),
  39. 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),
  40. books = (select count(*) from os_appointments o where cast(o.DateCreated as date) = workdate  and isnull(o.confirmedby, o.booked_by) = t.servrepid),
  41. 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')
  42.  
  43. from
  44. (
  45. select cast(cor.datecreated as date) workdate, cor.ServRepID, correspondenceID
  46. from CRM_Correspondence cor
  47. where cor.datecreated >= '2017-01-01'
  48. group by cast(cor.datecreated as date), cor.servrepid, correspondenceID
  49. ) t
  50. join ServiceReps sr on t.ServRepID = sr.ServRepID
  51. join CRM_Accounts ca on t.CorrespondenceID = ca.firstCorRecordID
  52. ) u on v.sr_name = u.sr_name and v.workdate = u.workdate
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top