0

I am rewriting code from MS Access to T-SQL (SQL Server 2008 R2) and this one had me stumped. I know I would need to use the CASE ... WHEN statement but this one is involving many columns and then count those that have '0's.

IIf(S.Knowledge1=0,1,0) +IIf(S.Routing1=0,1,0) +IIf(S.DataEntry1=0,1,0) +IIf(S.DataEntry2=0,1,0) +IIf(S.DataEntry3=0,1,0) +IIf(S.Scripting1=0,1,0) +IIf(S.Conduct1=0,1,0) +IIf(S.Conduct2=0,1,0) +IIf(S.Conduct3=0,1,0) AS CoachingCount
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Holly Cathcart
  • 93
  • 1
  • 1
  • 10
  • This site is for programming questions. We are not a code translation service. https://msdn.microsoft.com/en-us/library/hh213574.aspx – Marc B Aug 05 '15 at 21:26
  • That link you provided does NOT help me at all for what I am needing help with. And I have had a couple questions similar to my question above in StackOverFlow helped me in the past. Thanks! – Holly Cathcart Aug 05 '15 at 21:28
  • 1
    And Marc, please read my post a little slower and this link.....IIF doesn't work for 2008R2...it's new starting with 2012.........http://stackoverflow.com/questions/11540753/sql-server-2008-iif-statement-does-not-seem-enabled – Holly Cathcart Aug 05 '15 at 21:49
  • 1
    Holly, please have a try with `CASE ... WHEN`, show us how far you get, and describe any problem you encounter. Here is a starter: `select case when s.DataEntry1=0 then 1 else 0 end + case when s.DataEntry2=0 then 1 else 0 end AS CoachingCount FROM table1 AS s` – HansUp Aug 05 '15 at 21:54
  • 1
    HansUp - BINGO! Just did all the 16 columns exactly how you explained and it came out perfect. Thank you ! This one is NEW for me. Never knew that one can + the case whens together. Need to mark your answer as the right one somehow. – Holly Cathcart Aug 05 '15 at 22:12

1 Answers1

2

Your instinct is correct that CASE ... WHEN would be useful. But don't think in terms of one massive CASE ... WHEN expression as a substitute for all those Access IIf expressions. Instead use a separate CASE ... WHEN for each IIf ... and sum up their values as you did with the IIf expressions.

Here is a brief example based on only 2 of those fields to get you started: 

select
      case when s.DataEntry1=0 then 1 else 0 end
    + case when s.DataEntry2=0 then 1 else 0 end AS CoachingCount
FROM table1 AS s
HansUp
  • 95,961
  • 11
  • 77
  • 135