More Query Help

Right i've ran into another problem

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

Code:

---

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

Code:

---

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:

Code:

---

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

---

You can have multiple WHEN clauses, eg:

Code:

---

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
)

---

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

cheers again