SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Doing an IIF statement in SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

huynhtl
Posting Yak Master

107 Posts

Posted - 04/21/2008 :  15:55:30  Show Profile  Reply with Quote
Hi,
I'm having problem trying to do an iif.
This is what total of each month for now.
month total
01 2217.3700
02 2187.6600
03 2243.6500
04 2216.1000
05 2374.4200
06 2296.2200
07 2266.6000
08 2164.2600
09 2085.2400

The months are from 01-12 and 99. I want to combine month 12 and 99 as one.

This is the sql i have, but it comes back in error

SELECT postfiscalMonth,
Object, Title, IIf((postfiscalMonth = ('12'),
total+(select total from dbo.SFY08Staffmonths
where postfiscalmonth = ('99')),IIf(postfiscalMonth = ('24'),
total+(select total from dbo.SFY08Staffmonths
where postfiscalmonth = ('25')), total))) AS Total FROM dbo.SFY08Staffmonths

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '='.
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near ','.
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near ','.





nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 04/21/2008 :  15:58:58  Show Profile  Visit nr's Homepage  Reply with Quote
case when postfiscalMonth = '12' then ... else ... end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

huynhtl
Posting Yak Master

107 Posts

Posted - 04/21/2008 :  16:40:46  Show Profile  Reply with Quote
I know i can do a case when, but i was just wondering if i can do an iif instead and what did i do wrong to have that post as an error/
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36845 Posts

Posted - 04/21/2008 :  16:42:11  Show Profile  Visit tkizer's Homepage  Reply with Quote
You can't use IIf in T-SQL.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

huynhtl
Posting Yak Master

107 Posts

Posted - 04/21/2008 :  16:51:40  Show Profile  Reply with Quote
So the case when is the same thing as the iif then.
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 04/21/2008 :  17:19:44  Show Profile  Visit nr's Homepage  Reply with Quote
SELECT case when postfiscalMonth = '99' then '12' else postfiscalMonth end ,
sum(total)
from tbl
group by case when postfiscalMonth = '99' then '12' else postfiscalMonth end


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 04/21/2008 :  17:22:07  Show Profile  Visit nr's Homepage  Reply with Quote
SELECT case when postfiscalMonth = '99' then '12' else postfiscalMonth end ,
sum(total)
from tbl
group by case when postfiscalMonth = '99' then '12' else postfiscalMonth end

Sorry - had a problem with my connection. Re-reading the question this is probably more like what you want.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 04/21/2008 17:23:27
Go to Top of Page

huynhtl
Posting Yak Master

107 Posts

Posted - 04/21/2008 :  17:43:49  Show Profile  Reply with Quote
Thanks. that works great!!!!!!!!!!!!!!!!!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000