Conditionally replacing values in SELECT

Question

Hi,

I've got a query that returns the cost of wages for a given member of staff

SELECT     totalhours * staffbaserate AS TotalCost
FROM         newrotaRaw
WHERE     staffref = @staffref

However I need to do an additional bit of maths if the returned value is > 105. The bit of maths I need to do is that if the value is < 105 the value is returned as is, however if the value is > 105 then I need to do value * 1.128.

For example:

John Smith's Cost is 90 therefore the query should return 90

David Smith's Cost is 140 therefore the query should return 157.92

I'm sure there's some way to use replace to get it to do what I want but I've only ever used replace for exact matches, in this case the replace is conditional on the value.

Any help would be much appreciated!

Answer 1

SELECT    totalhours * staffbaserate * (CASE WHEN (totalhours * staffbaserate) < 105 THEN 1 ELSE 1.128 END) AS TotalCost
FROM         newrotaRaw
WHERE     staffref = @staffref

Answer 2

You're looking for the CASE statement.

Answer 3

Hi,

Try something like this.

SELECT     TotalCost = 
    CASE
     WHEN (totalhours * staffbaserate) < 105 THEN (totalhours * staffbaserate)
     ELSE (totalhours * staffbaserate) * 1.128
    END
FROM         newrotaRaw
WHERE     staffref = @staffref

Answer 4

SELECT     
    CASE WHEN totalhours * staffbaserate <= 105 THEN totalhours * staffbaserate 
         ELSE totalhours * staffbaserate * 1.128 END AS TotalCost
FROM         newrotaRaw
WHERE     staffref = @staffref