Converting Access Query to SQL Server Query

[wintonsl]

Would anyone know how to convert this Access Query to SQL Server Query?

Format((([ResponsivenessQ1]+[ResponsivenessQ2]+[ResponsivenessQ3]+[ResponsivenessQ4])/(IIf([ResponsivenessQ1]>0,1,0)+IIf([ResponsivenessQ2]>0,1,0)+IIf([ResponsivenessQ3]>0,1,0)+IIf([ResponsivenessQ4]>0,1,0))),"Standard")

What this query is doing in Access is Averaging numbers that are not equal to 0. If the number is equal to 0, it should not be averaged in. The average is set to 2 decimal places.

[scripto]

try this

-----------optional just for testing -----------------------
declare @resq1 int,@resq2 int,@resq3 int,@resq4 int
set @resq1 = -1
set @resq2 = 2
set @resq3 = 0
set @resq4 = 5
----------------end optional-----------------------------------

select convert(decimal(10,2),(@resq1+@resq2+@resq3+@resq4 )/((case when @resq1 > 0 then 1 else 0 end)+
(case when @resq2 > 0 then 1 else 0 end)+
(case when @resq3 > 0 then 1 else 0 end)+
(case when @resq4 > 0 then 1 else 0 end)))