# Thread: Percentage Formula in SQL

1. Registered User
Join Date
Oct 2014
Posts
291

## Answered: Percentage Formula in SQL

Code:
`ROUND(A1.CONTRACT_PKG_PRICE * (1-ISNULL(I.DISCOUNT,0)),2)`
A1.CONTRACT_PKG_PRICE is a decimal data type (18,2) I.DISCOUNT is a decimal data type (8,6)

If I hard code the percentage into the I.DISCOUNT field I get the intended results, however if I don't I get the A1.CONTRACT_PKG_PRICE amount.

Any thoughts?

## "The formula seems to work out for me. You have the nulls covered well enough: Code: ```create table #temp (CONTRACT_PKG_PRICE decimal(18, 2), discount decimal (8, 6)) insert into #temp values (1000.00, 0.2), -- 20% off (2000.00, null), --Nothing off (3000.00, 1.0), -- Free (4000, 0.0), -- nothing off again (null, null), -- Nothing at no discount (null, .5) -- 50% off of nothing select ROUND(CONTRACT_PKG_PRICE * (1-ISNULL(DISCOUNT,0)),2) from #temp``` What is the result you are getting?"

3. Registered User
Join Date
Jan 2003
Location
Massachusetts
Posts
5,862
The formula seems to work out for me. You have the nulls covered well enough:
Code:
```create table #temp
(CONTRACT_PKG_PRICE decimal(18, 2),
discount decimal (8, 6))

insert into #temp values
(1000.00, 0.2), -- 20% off
(2000.00, null), --Nothing off
(3000.00, 1.0), -- Free
(4000, 0.0), -- nothing off again
(null, null), -- Nothing at no discount
(null, .5) -- 50% off of nothing

select ROUND(CONTRACT_PKG_PRICE * (1-ISNULL(DISCOUNT,0)),2)
from #temp```
What is the result you are getting?

4. Registered User
Join Date
Oct 2014
Posts
291
Contract Pkg Price * 1 not the discount. I'm wondering if it has something to do with my join. I'll follow up, thanks for replying MCrowley at least I am know I'm not completely going crazy.

5. Registered User
Join Date
Oct 2014
Posts
291