rounding - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

rounding

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-06-02 : 09:56:42

I have the following statement and I want to round the final value(gbkmut.bdr_hfl)two decimal places.

begin
UPDATE gbkmut
SET gbkmut.bdr_hfl = gbkmut.bdr_hfl - (SELECT SUM(inserted.bdr_hfl) FROM inserted WHERE inserted.freefield3 = 'Rebate')
WHERE reknr = ' 1040'
end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-02 : 09:59:09

Use ROUND() function:-

http://doc.ddart.net/mssql/sql70/ra-rz_20.htm

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-06-02 : 10:00:16

I'm not sure where to stick it. I've tried

Round(gbkmut.bdr_hfl,2) and I've also tried putting it around the select sum.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-02 : 10:10:17

quote:
Originally posted by Vack

I'm not sure where to stick it. I've tried

Round(gbkmut.bdr_hfl,2) and I've also tried putting it around the select sum.

begin
UPDATE gbkmut
SET gbkmut.bdr_hfl = ROUND(gbkmut.bdr_hfl - (SELECT SUM(inserted.bdr_hfl) FROM inserted WHERE inserted.freefield3 = 'Rebate'),2)
WHERE reknr = ' 1040'
end

raky
Aged Yak Warrior

767 Posts

Posted - 2008-06-02 : 10:12:14

quote:
Originally posted by Vack

I have the following statement and I want to round the final value(gbkmut.bdr_hfl)two decimal places.

begin
UPDATE gbkmut
SET gbkmut.bdr_hfl = gbkmut.bdr_hfl - (SELECT SUM(inserted.bdr_hfl) FROM inserted WHERE inserted.freefield3 = 'Rebate')
WHERE reknr = ' 1040'
end

TRY THIS

UPDATE gbkmut
SET gbkmut.bdr_hfl = ROUND(gbkmut.bdr_hfl - (SELECT SUM(inserted.bdr_hfl) FROM inserted WHERE inserted.freefield3 = 'Rebate'),2)
WHERE reknr = ' 1040'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-02 : 10:14:41

Never round until last statement.
Also avoid running the correlated subquery more than once.

DECLARE	@Sum MONEY

SELECT	@Sum = SUM(bdr_hfl)
FROM	inserted
WHERE	freefield3 = 'Rebate'

SET	@Sum = COALESCE(@Sum, 0)

UPDATE		gbkmut
SET		bdr_hfl = ROUND(bdr_hfl - @Sum, 2)
WHERE		reknr = ' 1040'

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources