Existing Tables: Projects - projectID - name - rate Shifts - shiftID (PK) - projectID (FK) - name - startTime - rate [Shifts] ID projectID userID shift startTime baseRate addition 1 1 1 Shift A 1900-01-01 08:00:00.000 15.00 10% 1 1 1 Shift B 1900-01-01 17:00:00.000 15.00 2 [Projects] ID project rate 1 Project A 10
Logic: shift rate consists of two fields ( baserate + addition ) where [addition] can be either a percent or a fixed dollar amount. I have a SELECT query to determine the proper rate that needs to be applied, but there is a bug:
when the [addition] is a fixed dollar amount, the base rate multiplies the addition instead of adding it
SELECT CASE WHEN ISNULL(s.rate,0) = 0 THEN p.rate ELSE s.rate END * CASE WHEN CHARINDEX('%',s.addition)>0 THEN (1+CAST(REPLACE(s.addition,'%','') AS FLOAT)/100) ELSE 1+CAST(ISNULL(s.addition,0) AS FLOAT) END AS finalRate FROM Projects p INNER JOIN Shifts s ON p.ID = s.projectID
So, I need to fix this line **ELSE 1+CAST(ISNULL(sw.addition,0) AS FLOAT) **
Please note that the query snippet is part of a much larger query, so it cannot be changed too much.