When I use a statement and set in same query, we get
Incorrect syntax near ”
DECLARE @Enter int DECLARE @Month INT DECLARE @year INT DECLARE @Delayed INT = NULL DECLARE @NextMonth INT = null DECLARE @AfterNextMonth INT = NULL DECLARE @beyond INT = null SELECT @Enter = MONTH(GETDATE()), @year = YEAR(GETDATE()) SELECT * SET @Delayed = IIF(MONTH(ExpectedDeliveryDate) < MONTH(GETDATE()) AND YEAR(ExpectedDeliveryDate) < YEAR(GETDATE()), OpemAmount,'Null') SET @Month = IIF(MONTH(ExpectedDeliveryDate) = MONTH(GETDATE()), OpemAmount,'Null') SET @NextMonth = IIF(MONTH(ExpectedDeliveryDate) = MONTH(GETDATE()) + 1, OpemAmount,'Null') SET @AfterNextMonth = IIF(MONTH(ExpectedDeliveryDate) = MONTH(GETDATE()) +2, OpemAmount,'Null') SET @beyond = IIF(MONTH(ExpectedDeliveryDate) > MONTH(GETDATE()) AND YEAR(ExpectedDeliveryDate) > YEAR(GETDATE()) OR MONTH(ExpectedDeliveryDate) = Null , OpemAmount,'Null') @Delayed AS Delayed, @Month AS '@Enter', @NextMonth AS '@NextMonth', @AfterNextMonth AS '@AfterNextMonth' , @beyond AS '@beyond' FROM dbo.vwOpenSalesOrders
we declare 5 variables, what would to write when we get error in @Delayed after finisshed the statement.
Advertisement
Answer
SELECT *, IIF(MONTH(ExpectedDeliveryDate) < MONTH(GETDATE()) AND YEAR(ExpectedDeliveryDate) < YEAR(GETDATE()), 'OpemAmount','Null') AS Delayed, IIF(MONTH(ExpectedDeliveryDate) = MONTH(GETDATE()), 'OpemAmount','Null') AS Months, IIF(MONTH(ExpectedDeliveryDate) = MONTH(GETDATE()) + 1,'OpemAmount','Null') AS NextMonth, IIF(MONTH(ExpectedDeliveryDate) = MONTH(GETDATE()) +2, 'OpemAmount','Null') AS AfterNextMonth, IIF(MONTH(ExpectedDeliveryDate) > MONTH(GETDATE()) AND YEAR(ExpectedDeliveryDate) >= YEAR(GETDATE()) OR MONTH(ExpectedDeliveryDate) = Null , 'OpemAmount','Null') AS beyond
FROM dbo.vwOpenSalesOrders