If condition with CTE in sql-CodePudding

I am trying to Create a view where i need to put condition on year and I am trying below code but I get an error. Can someone please suggest if its possible . And the output which is required I have to use CTE only. Error is as below : incorrect Syntax near ‘if’

Code is as below :

Declare @year varchar(20)
Set @year = ‘2022’;

With CTE as 
( 
    If @year = ‘2022’ 
    Begin 
    (
        Select 
            @year , 
            Prev_date = (select    DATEADD(month, -1,GETDATE()))
    )
End; 
Else 
  Begin 
(
Select @year,
Prev_date=  (select    DATEADD(month, -2,GETDATE()))
)
  End;
)
Select * 
From CTE

CodePudding user response：

I'm not entirely sure what you want to achieve with your cte, but concerning the condition, you can try to put the following into your view / query / whatever:

Declare @year varchar(20)
Set @year = '2022';

SELECT [year] = @year, [prev_date] = DATEADD(month, -1 * CASE WHEN @year = '2022' THEN 1 ELSE 2 END, GETDATE())

CodePudding user response：

IF, BEGIN and END are related to the procedural code, and cannot be used in this context.

Your code, without further specification, can be replaced with the following:

DECLARE @Year varchar(20);
SET @Year = '2022';

WITH CTE AS
( 
    SELECT Year = @year,
        PrevDate = DATEADD(month, -1,GETDATE())
)
SELECT * 
FROM CTE;

A note and a piece of advice: it looks (am I wrong?) that you are at the beginning of the learning. Try to keep coding guidelines for SQL Server, please. I have made some quick improvements. An example link to follow: https://blog.sqlauthority.com/2008/09/25/sql-server-guidelines-and-coding-standards/

CodePudding user response：

You can use simple effective query rather than going for CTE which should be used for complex queries.

Declare @year varchar(20) Set @year = '2023';
select @year [Year], prev_date = case when @year='2022' then  (Select  DATEADD(month, -1,GETDATE())) else     DATEADD(month, -2,GETDATE()) end

CodePudding user response：

You have not give the error and what database you are using. I suppose you sql should be syntax error, please try this

Declare @year varchar(20)
Set @year = ‘2022’
With CTE as 
( 
    If @year = ‘2022’ 
    Begin 
    {
        Select 
            @year , 
            (select DATEADD(month, -1,GETDATE())) as previous_date
    }
    END;
)
Select * 
From CTE

BEGIN ... END; must use with {..} in the case use have multiple statments in between. Hope this help.