I need to use a CASE expression in a WHERE clause but am getting an error. The code as follows -
...
WHERE (CASE WHEN month(getdate()) > 1 THEN
left([table_1].[object_1], 4) = year(getdate()) -- getting error on this equals sign
ELSE
left([table_1].[object_1], 4) = year(getdate())-1
END
)
The code isn't an exact copy but it's the error on the first '=' that is always flagged ('incorrect syntax')
Thanks
WHERE
(CASE WHEN month(getdate()) > 1 THEN CAST(left([table_1].[object_1], 4) AS INT)
ELSE CAST(left([table_1].[object_1], 4) AS INT) +1
END
) = year(getdate())
Thanks. There was a bit more to it that I thought if left off would simplify it. Basically if the month(getdate()) is greater than 1 then I need the current year (of object) and any prior months (of same) i.e.
201702, 201701...
CASE WHEN month(getdate()) >1 THEN left([table_1].[object_1], 4) = year(getdate()) AND right([table_1].[object_1], 2) < month(getdate())
ELSE ...
Obviously syntax is again wrong. Thanks for any help here.
WHERE
(CASE WHEN month(getdate()) > 1 THEN CAST(left([table_1].[object_1], 4) AS INT)
ELSE CAST(left([table_1].[object_1], 4) AS INT) +1
END
) = year(getdate())
AND
(CASE WHEN month(getdate()) > 1 THEN right([table_1].[object_1], 2)
ELSE ...
END
) < month(getdate())
To be easy: generate the first part of your CASE
like a column , inside a CTE
and then complete the WHERE
CTE AS (
SELECT ...
,CASE WHEN month(getdate()) > 1 THEN CAST(left([table_1].[object_1], 4) AS INT)
ELSE CAST(left([table_1].[object_1], 4) AS INT) +1
END AS myYear
,CASE WHEN month(getdate()) > 1 THEN right([table_1].[object_1], 2)
ELSE right([table_1].[object_1], 2)
END myMonth
FROM
...
)
SELECT ...
FROM
CTE
WHERE
myYear = year(getdate()) /* this is not SARGable*/
AND
myMonth < month(getdate())
Thanks stepson. I've got it working but finally need to cater for Jan going back into the previous year as follows. There seems too much repetition though and ideally I need to shorten the 2 parts (where month(getdate) > 1 and where month(getdate) = 1).
(s_num and i_num are now named.). Thanks.
WHERE
(
case when MONTH(getdate()) > 1 then
cast(LEFT(s_num, 4) AS INT) end = YEAR(getdate())
AND
(case when MONTH(getdate()) > 1 then
CAST(right(s_num, 2) AS INT) end) < month(getdate())
)
OR
(
case when MONTH(getdate()) > 1 then
cast(LEFT(i_num, 4) AS INT) end = YEAR(getdate())
AND
(case when MONTH(getdate()) > 1 then
CAST(right(i_num, 2) AS INT) end) < month(getdate())
)
or
(case when MONTH(getdate()) = 1 then
cast(LEFT(s_num, 4) AS INT) end = YEAR(getdate())-1
AND
(case when MONTH(getdate()) = 1 then
CAST(right(s_num, 2) AS INT) end) <= 12
)
OR
(
case when MONTH(getdate()) = 1 then
cast(LEFT(i_num, 4) AS INT) end = YEAR(getdate())-1
AND
(case when MONTH(getdate()) = 1 then
CAST(right(i_num, 2) AS INT) end) <= 12
)
Maybe this instead?:
WHERE
(MONTH(GETDATE()) > 1 AND (s_num < CONVERT(varchar(6), GETDATE(), 112) OR
(i_num < CONVERT(varchar(6), GETDATE(), 112))))
OR
(MONTH(GETDATE()) = 1 AND (s_num < CONVERT(varchar(6), DATEADD(YEAR, -1, GETDATE()), 112) OR
(i_num < CONVERT(varchar(6), DATEADD(YEAR, -1, GETDATE()), 112))))
1 Like