CODEWITHSUNDEEP
sqlsql-server
Wyseguy
Wyseguy

Reputation: 21

How to create this formula in SQL

I've switched over to SQL for a report, previously this was produced in excel.

The formula was;

=IF([@[Qualifying Premium]]=0, 0, IF(C13="Team One", (10%-X13)*BC13, (20%-X13)*BC13))

Column BC was the monthly salary and this gave out an amount to represent how much bonus they missed out on if they hit the full % Bonus.

I've recreated this in SQL AS

SUM(CASE WHEN Qualifying Premium='0' THEN 0 WHEN Team='Team One' THEN 10%-[BONUSQ%]*Monthly Salary ELSE 20%-[BONUSQ%]*Monthly Salary END)

The original Output from excel would be for example '£200' However from SQL i just get 0.00 or 10.00. Can anyone explain what this issue is?

Upvotes: 0

Views: 49

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

The % sign in SQL means something other than percentage, it means the modulus (remainder operation). Try the following version:

SUM(CASE WHEN Qualifying Premium = 0 THEN 0
         WHEN Team = 'Team One' THEN (0.1 - [BONUSQ%])*[Monthly Salary]
         ELSE (0.2 - [BONUSQ%])*Monthly Salary END)

Upvotes: 1

Related Questions