JannetteChristie
Board Regular
- Joined
- Dec 14, 2015
- Messages
- 119
Hello,
I am running the following query in SSMS without any issues.
I am running the following query in SSMS without any issues.
However I get the following error when trying to run the same query in excel.VBA Code: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