SQL Server View

Transact-SQL
1

Hello Experts,

I came through a problem. I am using SSMS 2014. i need to create a view for getting month list of previous three years. For that i am using below code . But its showing error:-incorrect syntax near Declare. Because i think in view we cannot use DECLARE and SET keyword .
So please suggest me any solution for the problem.

DECLARE @StartDATE DATETIME;
SET @StartDATE = '01-01-2000';

WITH Tens (N) AS
(
SELECT 0 N UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0
),
Thousands (N) AS
(
SELECT t1.N FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3
),
Tally AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) N FROM Thousands
)
SELECT MonthNum =
CASE T.N % 12
WHEN 0 THEN 12
ELSE T.N % 12
END
, DATEPART(YY,DATEADD(M,N-1,@StartDATE)) [YEAR]
, DATENAME(MM,DATEADD(M,N-1,@StartDATE)) [MONTH]
FROM Tally T
WHERE DATEPART(YY,DATEADD(M,N-1,@StartDATE)) <= YEAR(GETDATE()) and DATEPART(YY,DATEADD(M,N-1,@StartDATE)) >= YEAR(GETDATE())-3;

Quick responses will be highly appreciable.

Thanks in advance ,
Manita Raj

2

Use another CTE to feed in the @StartDATE:

DECLARE @StartDATE DATETIME;
SET @StartDATE = '01-01-2000';

;WITH cteStartDATE AS (
    SELECT @StartDATE AS StartDATE
), 
Tens (N) AS
(
SELECT 0 N UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0
),
Thousands (N) AS
(
SELECT t1.N FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3
),
Tally AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) N FROM Thousands
)
SELECT MonthNum =
CASE T.N % 12
WHEN 0 THEN 12
ELSE T.N % 12
END
, DATEPART(YY,DATEADD(M,N-1,StartDATE)) [YEAR]
, DATENAME(MM,DATEADD(M,N-1,StartDATE)) [MONTH]
FROM Tally T
WHERE DATEPART(YY,DATEADD(M,N-1,StartDATE)) <= YEAR(GETDATE()) and DATEPART(YY,DATEADD(M,N-1,StartDATE)) >= YEAR(GETDATE())-3;
3

Almost...

WITH cteStartDate AS (
SELECT CAST('2000-01-01' AS datetime) AS StartDate
),

Then you need to JOIN to get the value from the CTE:

FROM Tally t
CROSS JOIN cteStartDate sdt

Now we can reference the value:

, DATEPART(YY,DATEADD(M,N-1,sdt.StartDATE)) [YEAR]
, DATENAME(MM,DATEADD(M,N-1,sdt.StartDATE)) [MONTH]

With that said - you can get the same results with less code using:

   With datetally (MonthNum, [YEAR], [MONTH])
     As (
 Select p.id + 1
      , year(dateadd(month, p.id, dateadd(year, datediff(year, 0, getdate()) - 3, 0)))
      , datename(month, dateadd(month, p.id, dateadd(year, datediff(year, 0, getdate()) - 3, 0)))
   From (Select row_number() over(Order By ac.[object_id]) - 1 As id From sys.all_columns ac) As p
  Where p.id < 48 
        )
 Select *
   From dateTally;

I would not use [YEAR] or [MONTH] as column names - they are reserved words and it will make things harder for any queries using this view.

This view will give you the past 3 years and the current year up to the end of this year. Not sure why you need future dates but this will work.

For better functionality - I would return an actual start and end dates and let the consumer of the view parse the year/month/day/week/etc...

   With datetally (MonthNum, MonthStart, MonthEnd, NameMonth)
     As (
 Select p.id + 1
      , cast(dateadd(month, p.id, dateadd(year, datediff(year, 0, getdate()) - 3, 0)) As date)
      , eomonth(dateadd(month, p.id, dateadd(year, datediff(year, 0, getdate()) - 3, 0)))
      , datename(month, dateadd(month, p.id, dateadd(year, datediff(year, 0, getdate()) - 3, 0)))
   From (Select row_number() over(Order By ac.[object_id]) - 1 As id From sys.all_columns ac) As p
  Where p.id < 48 
        )
 Select *
   From datetally;