Decimal is not stored - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Decimal is not stored

Author

Topic

Deltaur
Starting Member

29 Posts

Posted - 2012-02-15 : 08:52:28

Hi,

i got table where i have this field
PERCENT numeric (10,2) null

UPDATE VSK5 SET
PERCENT = (select isnull((abs(VSK5.purchases) - abs(VSK5.purchases2)) * -1 / (VSK5.purchases3- VSK5.purchases4) * 100.00 ,0))
FROM #TESTBASE VSK5

but the value doesnt store to database? what i am doing wrong?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-15 : 08:57:20

You are doing the update on a temp table - you know that?

No, you're never too old to Yak'n'Roll if you're too young to die.

Deltaur
Starting Member

29 Posts

Posted - 2012-02-15 : 09:35:32

Yeah,

I know that. Is that problem in following update sequence?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-15 : 09:43:10

What do you mean with following update sequence?
Are there more updates than you have posted?

No, you're never too old to Yak'n'Roll if you're too young to die.

Deltaur
Starting Member

29 Posts

Posted - 2012-02-15 : 09:49:19

I have chain of update commands for that temporary table but they all are working correctly. I have checked that values are inserted correctly to temporary database. Only problem is in this query it just somehow doesn't calculate values (or calculates but update is not doing update towards database

).

i have taken the values from temp database and used exactly same "formula" to calculate fields and i got result as it should be but in sql query it just doesn't update anything to database.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-15 : 09:53:41

try with
[PERCENT] instead of PERCENT because it is a key word in sql.

No, you're never too old to Yak'n'Roll if you're too young to die.

Deltaur
Starting Member

29 Posts

Posted - 2012-02-15 : 09:55:49

Tried, but did not work. :(

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-15 : 09:59:09

post the ddl of the table, sample data in DML form and what the expected results are suppose toi be

Also, show us HOW the temp table is created..what the "String" of updates are, and what ultimately you do with the temp table

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Deltaur
Starting Member

29 Posts

Posted - 2012-02-15 : 10:27:47

CREATE TABLE #QUARTER
(
Ordernum integer null,
Seller varchar(100) null,
PrjTurnover integer null,
PrjPurchases integer null,
PrjInstPurc integer null,
CntMargin integer null,
Turnover integer null,
Purchases integer null,
InstPurchases integer null,
cntPercent numeric (10,2) null
)

INSERT INTO #QUARTER
select
pst.ordernumber,
pst.seller,
pst.turnovertot,
pst.purchasestot,
pst.contmargtot,
0,
0,
0,
0,
0.1

from psOrders pst
inner join psSellers on psSellers.seller = pst.seller
where orderdate between '2011-12-01' and '2012-01-31'
order by pst.seller

UPDATE VSK SET
Turnover = (select isnull((sum(amount)*-1),0)
from psBookkeep psk
where psk.ordernumber = VSK.ordernum and psk.accNumber between 3000 and 3999)
FROM #QUARTER VSK

UPDATE VSK2 SET
Purchases = (select isnull((sum(amount)*-1),0)
from psBookkeep psk
where psk.ordernumber = VSK2.ordernum and psk.accNumber between 4000 and 3999)
FROM #QUARTER VSK2

UPDATE VSK3 SET
InstPurchases = (select isnull((sum(amount)*-1),0)
from psBookkeep psk
where psk.ordernumber = VSK3.ordernum and psk.accNumber IN (4203,4214,4208,4205,4206,4209,4204))
FROM #QUARTER VSK3

UPDATE VSK4 SET
cntPercent = (select isnull((abs(VSK4.turnover) - abs(VSK4.purchases)) * -1 / (VSK4.turnovertot - VSK4.purchasestot) * 100.00 ,0))
FROM #QUARTER VSK4

So here is the whole script. As you see i just "pump" data from other table to temp table. And goal is count all the data as ready as it can be calculated in server "end". And all the other steps is working well just that part of counting decimals isn't working.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-15 : 10:39:01

I can't believe that this will run without any errors!
UPDATE VSK4 SET
cntPercent = (select isnull((abs(VSK4.turnover) - abs(VSK4.purchases)) * -1 / (VSK4.turnovertot - VSK4.purchasestot) * 100.00 ,0))
FROM #QUARTER VSK4

see the red part as ONE example.

So what you are REALLY doing seems to be different...?

No, you're never too old to Yak'n'Roll if you're too young to die.

Deltaur
Starting Member

29 Posts

Posted - 2012-02-15 : 11:01:14

Hmm.. it works believe me or in excel it does :)

Example,

Excel formula
=(A1-B1)*-1 / (C1-D1) * 100

put values to cells (A1,B1,C1,D1) :
-150000 160000 160000 -180000

result should be -91,17647059% and 91,17647059% if corrected like below.

isnull((abs(VSK4.turnover) - abs(VSK4.purchases))
should be (noticed when i executed it),
isnull((abs(VSK4.purchases) - abs(VSK4.turnover))

Which means that seller has crossed his projected purchases when the value is under 100%. If in other hand the percent is ex. 125% it means seller has gone under in estimation for his purchases (which is good).

so way or the other SQL don't give me those kinda values. I think i have to figure that formula for sql in other kinda way.

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-15 : 11:40:21

What do you do with #Quarter AFTER All of this?

SELECT * FROM #Quarter?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-15 : 11:42:49

Is this SQL Server?

No way this Runs

UPDATE VSK SET
Turnover = (select isnull((sum(amount)*-1),0)
from psBookkeep psk
where psk.ordernumber = VSK.ordernum and psk.accNumber between 3000 and 3999)
FROM #QUARTER VSK

FROM..FROM

???

I don't think so

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-15 : 11:44:06

Never find...I see the convolution now

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-15 : 11:53:25

Couldn't you do something like?


INSERT INTO #QUARTER  (
			  Ordernum
			, Seller
			, PrjTurnover
			, PrjPurchases
			, PrjInstPurc
			, CntMargin
			, Turnover
			, Purchases
			, InstPurchases
			, cntPercent
)

    SELECT
			pst.ordernumber,	
			pst.seller, 
			pst.turnovertot, 
			pst.purchasestot, 
			pst.contmargtot, 
			0, 
			isnull((sum(pskamount)*-1),0), 
			isnull((sum(pskamount)*-1),0), 
			0, 
			0.1 

	  FROM  psOrders pst
INNER JOIN  psSellers 
		ON  psSellers.seller = pst.seller
INNER JOIN  psBookkeep psk1
		ON  psk1.ordernumber = ???.ordernum
	   AND  psk1.accNumber between 3000 and 3999
INNER JOIN  psBookkeep psk2
		ON  psk2.ordernumber = ???.ordernum
	   AND  psk2.accNumber IN (4203,4214,4208,4205,4206,4209,4204))
	   ...ect
	 WHERE  orderdate between '2011-12-01' and '2012-01-31'
  ORDER BY  pst.seller


UPDATE VSK SET
Turnover = (select isnull((sum(amount)*-1),0) from psBookkeep psk
where psk.ordernumber = VSK.ordernum and psk.accNumber between 3000 and 3999)
FROM #QUARTER VSK

It is a mess however, and I don't "see" what you a re doing

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources