SQLTeam.com | Weblogs | Forums

Convert IF statement to CASE


#1

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


#2

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