Create Temp Table and Insert Data in to Temp Table using Dynamic Query sp_executesql
-- create temp table
CREATE TABLE #tmp
(
Color VARCHAR(MAX),
TmpDays VARCHAR(MAX),
NoofOpp VARCHAR(MAX)
)
DECLARE @dynamicSQL nvarchar(2000),
@Numberofdays BIGINT = NULL
SET @Numberofdays = 8
--Create Dynamic Query
SET @dynamicSQL = 'SELECT CASE
WHEN Color = ''LIGHT_GREEN'' THEN ''#90EE90''
WHEN Color = ''GREEN'' THEN ''#006400''
WHEN Color = ''LIGHT_BLUE'' THEN ''#ADD8E6''
WHEN Color = ''BLUE'' THEN ''#0000CD''
WHEN Color = ''LIGHT_ORANGE'' THEN ''#FFA500''
WHEN Color = ''ORANGE'' THEN ''#FF4500''
WHEN Color = ''RED'' THEN ''#FF0000''
END AS Color,
CASE
WHEN Color = ''LIGHT_GREEN'' THEN ''0-7 Days''
WHEN Color = ''GREEN'' THEN ''8-15 Days''
WHEN Color = ''LIGHT_BLUE'' THEN ''16-25 Days''
WHEN Color = ''BLUE'' THEN ''26-35 Days''
WHEN Color = ''LIGHT_ORANGE'' THEN ''36-45 Days''
WHEN Color = ''ORANGE'' THEN ''46-60 Days''
WHEN Color = ''RED'' THEN ''60 > Days''
END AS TmpDays,
COUNT (Opportunity_Code) AS NoofOpp
FROM dbo.View_OpportunityHeaderDetails
WHERE [Status] != ''deleted''
AND VersionNo = 0 '
IF @Numberofdays IS NOT NULL
SET @dynamicSQL = @dynamicSQL + ' AND DATEDIFF(dd, LastmodifyDate,GETDATE()) > ' + CONVERT(NVARCHAR(MAX), @Numberofdays ) + ' GROUP BY Color '
ELSE
SET @dynamicSQL = @dynamicSQL + ' GROUP BY Color
Join the conversation! Your thoughts help the community grow.