CODEWITHSUNDEEP
sql-server-2008roundingcase-when
MHeath
MHeath

Reputation: 81

Case When unable to determine equal values

I am trying to confirm the values my database is calculating are correct. The query that I have written gives me this information. The Pull_through column is generated by the database and the column labeled as CalcQuantity is what I am using to check the database.

SELECT RPT.PayPeriod
      ,RPT.Employee_Id
      ,RPT.Plan_Id
      ,RPT.Commission
      ,Pull_through
      ,Modifier
      ,ROUND(Commission * Modifier,2) AS CalcQuantity
      ,CASE WHEN Pull_through = Commission * Modifier then 'Correct'
            WHEN Pull_through <> Commission * Modifier then 'ERROR'
            END AS Confirmed
FROM Compensation..IN_Pullthrough PULL
 left join RPT
  on PULL.Employee_id = RPT.Employee_Id and PULL.Pay_Date = RPT.PayPeriod
WHERE PULL.Pay_Date = '2016-02-12'

The calculation is working which I have been able to confirm visually but the CASE WHEN statement is not working correctly. Originally I thought it was because I needed to round my decimal places but the Confirmed results are still showing "ERROR" for any value that does not have a zero. I've provided a screenshot of the relevant columns so you can see the query results.

enter image description here

Is the issue with the CASE WHEN because of the zeros or does rounding not make the results equal according to SQL?

I have to update the information every month and run a stored procedure for the database to calc new results which is why I have built this audit.

Thanks in advance!

Upvotes: 0

Views: 33

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521639

This answer is a bit speculative, but one thing you could try is to compare the LHS and RHS of your CASE statement only up to the second decimal place:

CASE WHEN ROUND(Pull_through, 2) = ROUND(Commission * Modifier, 2)
    THEN 'Correct'
    ELSE 'ERROR'
END AS Confirmed

Upvotes: 1

Related Questions