I'm having trouble converting the following IF statement to a CASE statement. Any help appreciated, thanks.
if MONTH(getdate()) >1 and cast(left([s_num], 4) AS INT) = year(getdate())
and month(getdate()) >1 and cast(right([s_num], 2) AS INT) < month(getdate())
OR
month(getdate()) >1 and cast(left([i_num], 4) AS INT) = year(getdate())
and month(getdate()) >1 and cast(right([i_num], 2) AS INT) < month(getdate())
then 1
ELSEIF
month(getdate()) = 1 and cast(left([s_num], 4) AS INT) = year(getdate())-1
and month(getdate()) = 1 and cast(right([s_num], 2) AS INT) <=12
OR
month(getdate()) = 1 and cast(left([i_num], 4) AS INT) = year(getdate())-1
and month(getdate()) = 1 and cast(right([i_num], 2) AS INT) <=12
then 1
else 0 end
I would think something like this would work. I am simply copying and pasting your logic to a case expression. There may be opportunities to make the logic more compact/efficient/readable.
CASE
WHEN
MONTH(getdate()) >1 and cast(left([s_num], 4) AS INT) = year(getdate())
and month(getdate()) >1 and cast(right([s_num], 2) AS INT) < month(getdate())
OR
month(getdate()) >1 and cast(left([i_num], 4) AS INT) = year(getdate())
and month(getdate()) >1 and cast(right([i_num], 2) AS INT) < month(GETDATE())
THEN 1
WHEN
month(getdate()) = 1 and cast(left([s_num], 4) AS INT) = year(getdate())-1
and month(getdate()) = 1 and cast(right([s_num], 2) AS INT) <=12
OR
month(getdate()) = 1 and cast(left([i_num], 4) AS INT) = year(getdate())-1
and month(getdate()) = 1 and cast(right([i_num], 2) AS INT) <=12
THEN 1
ELSE 0
END