Create view using variables

Transact-SQL
1

Hi,

I need help in creating view based on below query, not sure how to use the variables in view creation

DEClare @CurrentQTR varchar(10),@CurrentMonth varchar(10) , @CurrentYear varchar(5)
SET @CurrentQTR = (select timeQuarter from CalendarTable where timeDate = CONVERT(Date, GetDate(), 101))
SET @CurrentMonth = (select timeMonth from CalendarTable where timeDate = CONVERT(Date, GetDate(), 101))
SET @CurrentYear = (select timeYear from CalendarTable where timeDate = CONVERT(Date, GetDate(), 101))

SELECT * FROM
(
select DISTINCT
LocationName ,
sum (CASE when Month = @CurrentMonth then TotalDeflation else 0 END) as MonthTotal,
sum (CASE when [Quarte]r = @currentQTR then TotalDeflation else 0 END) as QTRTotal,
sum (CASE when CurrentYear = @CurrentYear then TotalDeflation else 0 END) as YearTotal
FROM
Summarytable

group by locationName ) A

thanks

2

You cannot use variables in views.
To get a solution you should really post DDL with test data. At a guess:

SELECT S.locationName
	,SUM(CASE WHEN S.[Month] = C.timeMonth THEN S.TotalDeflation ELSE 0 END) AS MonthTotal
	,SUM(CASE WHEN S.[Quarter] = C.timeQuarter THEN S.TotalDeflation ELSE 0 END) AS QTRTotal
	,COUNT(*) AS YearTotal
FROM Summarytable S
	JOIN CalendarTable C
		ON S.CurrentYear = C.timeYear
WHERE C.timeDate >= DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)
	AND C.timeDate < DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 1)
GROUP BY S.locationName;
3

If you want the equivalent of a "parameterized view", create an iTVF (inline table valued function).