-2

I have the following code in Access and I need it to work in sql, the Is numeric portion is throwing me off.

Sum([Employee Count]*IIf(IsNumeric([Length]),[Length],0)) AS Total_hours,
user1958651
  • 451
  • 1
  • 5
  • 8
  • 2
    For which DBMS do you want this? SQL is just a *language* it is *not* a DBMS product. –  Jan 21 '13 at 20:59
  • I can never tell why people throw negatives on posts... I guess you didn't show what you've tried already. No? Bunch of nazis here. If you're downvoting.. might be helpful if you explained yourself a little. In the name of progress. – sam yi Jan 22 '13 at 16:27

2 Answers2

2

You will replace the IIF() with a CASE expression. The IsNumeric() is valid in SQL Server:

Sum([Employee Count]*
    case when IsNumeric([Length]) = 1
          then [Length]
          else 0 end) AS Total_hours,
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
Taryn
  • 242,637
  • 56
  • 362
  • 405
0

You can also just filter "not isnumeric" out since 0 will not impact SUM aggregation.

select
Sum([Employee Count]*[Length]) AS Total_hours
...
where isnumeric([Length]) = 1

See code below.

declare @table table (abc varchar(100))
insert into @table
select '1'
union select '200'
union select '200A'

select sum(convert(int,abc))
from @table
where isnumeric(abc) = 1

sqlfiddle

sam yi
  • 4,806
  • 1
  • 29
  • 40