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
GPSPOWAsked:
Who is Participating?
 
GPSPOWConnect With a Mentor 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

Open in new window



Thanks

Glen
0
 
SimonCommented:
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
 
GPSPOWAuthor 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
All Courses

From novice to tech pro — start learning today.