When I use a statement and set in same query, we get
Incorrect syntax near ”
x
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