Percentage rate function...help!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Percentage rate function...help!

Author

Topic

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-08-11 : 10:51:09

I need an equation to change percentages according to what a certain amount is. I have this equation, but it doesn't work:

UPDATE tblHUDdata
SET RateCharged = 0.18
WHERE RefundAmount > 2500
OR
SET RateCharged = 0.21
WHERE RefundAmount BETWEEN 1800 AND 2500
OR
SET RateCharged = 0.23
WHERE RefundAmount BETWEEN 1200 AND 1800
OR
SET RateCharged = 0.28
WHERE RefundAmount BETWEEN 600 AND 1200
OR
SET RateCharged = 0.32
WHERE RefundAmount < 600

Any ideas? Thanks!

Brenda

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-11 : 10:57:04

try using case:
case when RefundAmount > 2500 then 0.18
when RefundAmount BETWEEN 1800 AND 2500 then 0.21
...
end

Go with the flow & have fun! Else fight the flow :)

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-08-11 : 11:17:16

What is wrong with this now?

UPDATE tblHUDdata
SET RateCharged
CASE WHEN RefundAmount > 2500 THEN 0.18
WHEN RefundAmount BETWEEN 1800 AND 2500 THEN 0.21
WHEN RefundAmount BETWEEN 1200 AND 1800 THEN 0.23
WHEN RefundAmount BETWEEN 600 AND 1200 THEN 0.28
WHEN RefundAmount < 600 THEN 0.32
END

Thanks for any help!

Brenda

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-11 : 11:18:54

you're missing an = sign

UPDATE tblHUDdata
SET RateCharged =
CASE WHEN RefundAmount > 2500 THEN 0.18...

Go with the flow & have fun! Else fight the flow :)

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-08-11 : 11:21:00

Thanks a bunch! It works! yeah!

Brenda

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources