• Status: Solved
• Priority: Medium
• Security: Public
• Views: 1001

# Convert Excel formula to SQL case statement

Can someone help me convert the following Excel IF statement to either a SQL case statement or MS-Query SQL statement:

CurMonth

=IF(MONTH(getdate())=1,
IF(MO=12,
IF(YR=YEAR(getdate())-1,1,0),0),
IF(MO=MONTH(getdate())-1,
IF(YR=YEAR(getdate()),1,0)))

CurYear

=IF(MONTH(getdate())=1,
IF(MO<=12,
IF(YR=YEAR(getdate())-1,1,0),0),
IF(MO<=MONTH(getdate())-1,
IF(YR=YEAR(getdate()),1,0)))

PriorYear

=IF(MONTH(getdate())=1,
IF(MO<=12,
IF(YR=YEAR(getdate())-2,1,0),0),
IF(MO<=MONTH(getdate())-1,
IF(YR=YEAR(getdate())-1,1,0)))

Thanks

Glen
0
GPSPOW
• 2
1 Solution

Commented:
Hi Glen, the statements in your question look like a mixture of Excel and TSQL. Can you explain a bit more about what you'd like to achieve and post a few rows of sample data.
0

Author Commented:
I've requested that this question be deleted for the following reason:

Was able to convert Excel IF statement to a SQL query.

Thanks for reviewing it.

Glen
0

Author Commented:
Here is my solution to convertin the IF statement to a SQL case statement:

``````case when MONTH(getdate())=1 then
case when MO=12 then
case when  YR = YEAR(GETDATE())-1 then 1 else 0 end
else
case when  MO = month(GETDATE()-1) and YR = YEAR(GETDATE()) then 1
else 0 end end end as CMO,

Case when Month(getdate())=1 then
case when MO <=12 then
case when YR = year(getdate())-1 then 1 else 0 end
else
case when MO <=month(getdate()-1)  and YR=Year(getdate()) then 1
else 0 end end end as CYR,

Case when Month(getdate())=1 then
case when MO <=12 then
case when YR = year(getdate())-2 then 1 else 0 end
else
case when MO <=month(getdate()-1)  and YR=Year(getdate()-1) then 1
else 0 end end end as PYR
``````

Thanks

Glen
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.