combine lead, appointment,phone entities in SSRS.

senario:

if we need to get the data from Lead, Appointment, Phone Entities.


IF OBJECT_ID('tempdb..#AllReportees') IS NOT NULL
        DROP TABLE #AllReportees
        CREATE TABLE #AllReportees
(
    createdate Date,
    uname varchar(50),
    ownids uniqueidentifier,
    phoneact varchar(50),
    statuscode varchar(50)
  
)

INSERT INTO #AllReportees
select CONVERT(VARCHAR, createdon, 106) AS

date1,createdbyname,ownerid,activityid,statuscodename from FilteredPhoneCall
union
select CONVERT(VARCHAR, createdon, 106) AS

date1,createdbyname,ownerid,activityid,statuscodename from FilteredAppointment
union
select CONVERT(VARCHAR, createdon, 106) AS date1,createdbyname,ownerid,leadid,statuscodename

 from  FilteredLead



 select *
 ,dbo.Getwrongnumbercount(dd.createdate,dd.uname) as wrongcall
  from (
select  ss.createdate,ss.uname,
 count(distinct fp.activityid) as Phone ,
  count(distinct fl.leadid) as lead,
 (select count(*) from FilteredPhoneCall where  (CONVERT(VARCHAR, createdon, 106)=

ss.createdate) and (statuscodename = 'Wrong Number' or statuscodename = 'No Response')  )as

wrong,
   count(distinct fa.activityid) as appointment from #AllReportees ss
 left OUTER JOIN FilteredSystemUser AS su  WITH (NOLOCK) ON su.systemuserid=ss.ownids
 left OUTER JOIN  FilteredPhoneCall as fp WITH (NOLOCK) ON  (fp.activityid=ss.phoneact)
 left OUTER JOIN FilteredLead fl WITH (NOLOCK) ON  (fl.leadid=ss.phoneact)
 left OUTER JOIN FilteredAppointment AS fa WITH (NOLOCK) ON (fa.activityid=ss.phoneact)
  WHERE  (DATEPART(week, ss.createdate) = DATEPART(week, GETDATE()))   group by

ss.createdate,uname
     ) dd