DECLARE @MinDate DATETIME = '20160102', @MaxDate DATETiME = DATEADD(yyyy, Year(Getdate()) - 1900 + 1, -1);
--With DateRanges_Cte(xDateRange) As (
Select * Into #TempTable
From (
Select Date from
(SELECT TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1)
Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
) Alldates Left Outer Join CRM..HolidaySetItems On Alldates.Date=HSIt_HolidayDate
Where DATENAME(DW, Date) Not In ('Saturday','Sunday') and HSIt_HolidayDate Is Null
) X
Select #TempTable.[Date],Telesales,[F2F Calls],[Projs opened],[Oppos opened],[QFU],[EQFU],[Oppos flipped],[TQFU/TQFU2/CO],[SOPs Opened]
From #Temptable Left Outer Join (
Select Convert(datetime,Convert(date,Oppo_Opened)) [Date], Max(dataComm.Telesales) Telesales, Max(dataComm.[F2F Calls]) [F2F Calls], Max(dataProj.[Projs opened]) [Projs opened]
, count(distinct Oppo_OpportunityId) [Oppos opened], Max(dataComm.QFU) [QFU], Max(dataComm.EQFU) [EQFU], Max(dataFlipped.[Oppos filipped]) [Oppos flipped]
, Sum(Case When Year(Oppo_Closed) >= 2016 Then 1 Else 0 End ) [SOPs Opened],Max([TQFU/TQFU2/CO]) [TQFU/TQFU2/CO]
from Opportunity
Left Outer Join
(Select Convert(datetime,Convert(date,comm_completedtime)) xDate, Sum(Case When comm_action='TELES' Then 1 Else 0 End) Telesales
, Sum(Case When comm_action='QFU' Then 1 Else 0 End) [QFU], Sum(Case When comm_action='EQFU' Then 1 Else 0 End) [EQFU]
, Sum(Case When comm_action In ('SALVI','MEETING','CPDP','DV') THen 1 Else 0 End) [F2F Calls]
, Sum(Case When comm_action In ('TSQFU','TSQFU2','CO') THen 1 Else 0 End) [TQFU/TQFU2/CO]
From Communication Inner Join Comm_Link On Comm_CommunicationId=CmLi_Comm_CommunicationId
Where Comm_Deleted Is Null and CmLi_Deleted IS Null and Year(comm_completedtime)>=2016 and Comm_Status='Complete' and Comm_ChannelId In (7,50013,50024)
Group by Convert(datetime,Convert(date,comm_completedtime))
) dataComm On dataComm.xDate=Convert(datetime,Convert(date,Oppo_Opened))
Left Outer Join
(Select Convert(datetime,Convert(date,proj_opened)) xDate, Count(proj_projectId) [Projs opened]
From Project
Where Proj_Deleted Is Null and Year(proj_opened)>=2016
Group by Convert(datetime,Convert(date,proj_opened))
) dataProj On dataProj.xDate=Convert(datetime,Convert(date,Oppo_Opened))
Left Outer Join
(Select Convert(datetime,Convert(date,oppo_flipped_date)) xDate, Count(distinct Oppo_OpportunityId) [Oppos filipped]
From Opportunity
Where Oppo_Deleted Is Null and Oppo_Description Not Like 'Auto :%' and Year(oppo_flipped_date)>=2016 and Oppo_ChannelId In (7,50013,50024)
Group by Convert(datetime,Convert(date,oppo_flipped_date))
) dataFlipped On dataFlipped.xDate=Convert(datetime,Convert(date,Oppo_Opened))
Where Oppo_Deleted Is Null and Oppo_Description Not Like 'Auto :%' and Oppo_ChannelId In (7,50013,50024) and Year(oppo_opened)>=2016
Group by Convert(datetime,Convert(date,oppo_opened))
) Alldata On #TempTable.[Date]=Alldata.[Date]
Order by 1
Drop table #TempTable
