More Query Help

[Strider]

Right i've ran into another problem

in my table i have the following columns:
i=integer, b=bit, m=smallmoney

iNoCoachMovements , bChargePerCoach, mCoachFee
3,                            1,                       50.00
3,                            0,                       100.00
0,                            0,                       100.00

so i need to perform a query on the details above.
the way it will work is that

if bChargePerCoach = 1 then 
    iNoCoachMovements * mCoachFee
elseif iNoCoachMovements > 0 Then
      mCoachFee
else 0
end if

is this possible in a SQL Query.

it works for the 1st part with the sql below:

 SUM(CASE WHEN (dbo.tbl_Contracts.bChargePerCoach = 1) THEN (dbo.tbl_Despatches.iNoCoachMovements * dbo.tbl_Contracts.mCoachFee) 
                      ELSE 0 END)

[si_the_geek]

You can have multiple WHEN clauses, eg:

SUM(
  CASE 
    WHEN (dbo.tbl_Contracts.bChargePerCoach = 1) THEN (dbo.tbl_Despatches.iNoCoachMovements * dbo.tbl_Contracts.mCoachFee) 
    WHEN (dbo.tbl_Contracts.iNoCoachMovements > 0) THEN (dbo.tbl_Contracts.mCoachFee)
  ELSE 0 
  END
)

[Strider]

si what can i say man... but legend... cant believe i missed that

cheers again