Start Free Trial

asked on

SQL Query Decimal Results

Hi,

The Query updates recordsets for the criteria at once . e.g . There are two recordsets the query is supposed to update the IssRate. After executing the query the result shows :

ID IssRate(float)
1. 1641900 77.44
2. 1642774 77.430824372759858 (expected 77.44)

I don't want query to round the amount to 2 decimals, because the issRate calculation is sometimes in decimals and not rounded

Following query generates different results for different recordsets :

Update A
set IssRate =(select CASE WHEN (isnull(SUM(B.RecQty),0) - isnull(SUM(B.IssQty) ,0)) =0 THEN 0 ELSE (isnull(SUM(B.RecCost),0) - isnull(SUM(B.IssCost),0) ) / (isnull(SUM(B.RecQty),0) - isnull(SUM(B.IssQty) ,0)) END from TransactionStock_WtdAvg B where B.ProductId = A.ProductId AND B.TransDate <= A.TransDate AND B.TransactionId <> A.TransactionId
from TransactionStock_WtdAvg A
WHERE
      ID IN (SELECT TOP (100) PERCENT ID from TransactionStock_WtdAvg
      WHERE A.ProductId = @ProductId AND A.TransDate >= @EntryDate AND A.TransactionType = 13 AND ISNULL(IssQty, 0) <> 0 OR
      A.ProductId = @ProductId AND A.TransDate >= @EntryDate AND A.TransactionType = 16 AND ISNULL(IssQty, 0) <> 0 OR
      A.ProductId = @ProductId AND A.TransDate >= @EntryDate AND A.TransactionType = 22 AND ISNULL(IssQty, 0) <> 0
      ORDER BY TRANSDATE,SORTORDER)

whats wrong in the query?

Thanks.

put case when field contains decimal place then round to 2 otherwise leave as is

Try this

Update TransactionStock_WtdAvg
set IssRate = round(isnull(select CASE WHEN (isnull(SUM(B.RecQty),0) - isnull(SUM(B.IssQty) ,0)) =0 THEN 0 ELSE (isnull(SUM(B.RecCost),0) - isnull(SUM(B.IssCost),0) ) /  (isnull(SUM(B.RecQty),0) - isnull(SUM(B.IssQty) ,0)) END from TransactionStock_WtdAvg B where B.ProductId = TransactionStock_WtdAvg.ProductId AND B.TransDate <= TransactionStock_WtdAvg.TransDate AND B.TransactionId <> TransactionStock_WtdAvg.TransactionId  ), 0), 2)
from TransactionStock_WtdAvg 
WHERE                 
      ID IN (SELECT TOP (100) PERCENT ID from   TransactionStock_WtdAvg )
     and  ProductId = @ProductId AND TransDate >= @EntryDate AND A.TransactionType in (13, 16, 22) AND ISNULL(IssQty, 0) <> 0

Open in new window

ASKER

As I mentioned I don't want to round, I want to use as is.
why query gives different results for different recordsets.

>> why query gives different results for different recordsets. <<

Because your IssRate column datatype is float, which is an approximate datatype, so results might vary.
http://msdn.microsoft.com/en-us/library/ms173773.aspx

try to use decimal datatype instead

ASKER

Tried changing datatype to decimal, same result :

ID IssRate(decimal (18,6))
1. 1641900 77.440000
2. 1642774 77.441577 (expected 77.440000)

well, keep the decimal datatype for sure.

Now your query is a bit messy, let's try with this new one.

Update A
set a.IssRate = b.sumrate
from TransactionStock_WtdAvg A
inner join (
	select productid, isnull(sum(b.reccost) - sum(b.isscost) / nullif(sum(b.RecQty) - sum(b.issqty), 0), 0) as sumrate
	from TransactionStock_WtdAvg 
	where transdate >= @EntryDate
	group by productid
) B on B.ProductId = A.ProductId 
WHERE 	A.ProductId = @ProductId AND 
	A.TransDate >= @EntryDate AND
	A.TransactionType in (13, 16, 22) AND 
	IssQty > 0

Open in new window

what are the values & types of the columns used in the formula for both the rows.

If you are running the query with different values it sure will return different results as you are dividing one value with another.

Use ROUND(ColumnName, 2, 1)

eg.

SELECT ROUND(77.445, 2, 1) will produce 77.44
and
SELECT ROUND(77.445, 2, 0) will produce 77.45

the third parameter tells the ROUND function to Truncate or Round

1 = Truncate
0 = Round

Use the method accordingly. Hope this helps.

ASKER

User generated image

your example is still not clear, where is the ProductID column there? Please post some sample data (your input) and the expected result (the output) you want from your query.

ASKER

This data is for one productid, the productid column is missing there.

Each row which is supposed to be updated should calculate the rate from previously updated rows from the same query.

e.g. in above example two rows (Sr#2, Sr#3) are filtered by the where clause (TransType =13 missing column & IssQty >0 ). 1st row(sr#2) is perfectly calculating the IssRate because there is no update before. But for 2nd row(Sr#3), it is considering old value for 1st row(i.e. 79 ) to calculate the Issrate.

After Update column in above example is the expected result.

Field to be updated for each row
IssRate = Sum(RecCost) - Sum(IssCost) / sum(RecQty) - Sum(IssQty)

Sr#2
Where : TransId <> 1641900 and TransDate <= 15/05/2011

IssRate = 21963.20 - 0 / 280 - 0 = 78.44

Sr#3

Where : TransId <> 1642774 and TransDate <=28/052011

IssRate = 21963.20 - 79 (Rate before update- should be updated by same query) / 279 - 0 = 78.43799283154122

Kindly advice what version of SQL are you in.

ASKER

SQL SERVER 2008

>>IssRate = 21963.20 - 79 (Rate before update- should be updated by same query) / 279 - 0 = 78.43799283154122<<

But if the IssRate formula is Sum(RecCost) - Sum(IssCost) / sum(RecQty) - Sum(IssQty), then the result for SR#3 should be 78.721146. How you end up substracting the 79 there??

Let's try to work with this example. Please try it and let me know if the new column "newissrate" I've added there is producing the correct result. If not please indicate why

declare @t table (
	sr int identity,
	id int,
	transid int,
	[date] date,
	recqty int,
	recrate decimal(18,6),
	reccost decimal(18,6),
	issqty int,
	issrate decimal(18,6),
	isscost decimal(18,6)
)

insert @t values(1640933, 100545, '2011-05-01', 280, 78.44, 21963.2, 0, 0, 0),
(1641900, 101204, '2011-05-10', 0, 0,0,1,79, 0),
(1642774, 101912,'2011-05-28', 0, 0,0,1,79, 0)

select * from @t


select *, 
	(select isnull((sum(b.reccost) - sum(b.isscost)) / nullif(sum(b.RecQty) - sum(b.issqty), 0), 0)  from @t as b
	 where b.sr < a.sr) as newissrate
  from @t a 
where issqty > 0

Open in new window

ASKER

The isscost in your example is 0. If you update the issCost = issQty * issRate then the result would be
78.43799283154122 and not 78.721146

with the same example, once the sr#2 is updated, the issRate would be 77.44 and IssCost would be 77.44. When you calculate issrate for sr#3, issRate should be 77.44 i.e 29963.20 - 77.44(new value)/ 279.
The issue here is, while calculating issrate for sr#3 it is taking old issrate 79 i.e 21963.20 - 79 /279 =
78.43799283154122.

Why Old IssRate is 79, it must be 78.44 ?? You must be rounding the Old issue rate implicitly or explicitly. Also what is the value of IssueCost as that is also used in your formula.

ASKER

Let me make it clear that 79 is not rounded value it was old Rate (pls see the table) i.e value before update.

This is wtd. avg. calculation. 1st row is purchase entry 2nd & 3rd rows are material out entries.
Before material was purchased with the rate of 79 so the issue rates were 79. Later purchase rate changed to 78.44 so all the material issues after that should calculate wt. avg. rate .
so the 1st row as you see is updated purchase rate. Now I want to calculate rates for material outs i.e. sr#2 and sr#3. old rate for them was 79 now i want it to be changed to 78.44 .

What is the value of ISSCost when you run this formula. I think it is set to 79 because if isscost is set to 78.44 the system the code attached is returning 78.44 but if it is set to 79 then it is returning 78.440000 & 78.437992 which is correct as you are issuing at a rate hogher than the recrate.

See attached Code.

sql1.sql

ASKER

IssCost = IssRate * IssQty

try example by Ralmada. He has updated issCost =0, change it to issRate * IssQty.

with the same example, I want Sr#3 issRate = 78.44

ASKER

Ghunaima, ur query updates the issrates first to 78.44 and the further calculates the wtd. avg. rate again with the different query.This is perfect.
but I want query to update the rates for each row and at the same time calculate issrates for subsequent rows in the same query

still not clear :) What is the sample data we should use?

This one?

insert @t values(1640933, 100545, '2011-05-01', 280, 78.44, 21963.2, 0, 0, 0),
(1641900, 101204, '2011-05-10', 0, 0,0,1,79, 0),
(1642774, 101912,'2011-05-28', 0, 0,0,1,79, 0)

or this other one???

insert into #t values(1640933, 1, 100545, '2011-05-01', 13, 280, 78.44, 21963.2, 0, 0, 0),
(1641900, 1, 101204, '2011-05-15', 16, 0, 0,0,1,79, 78.44),
(1642774, 1, 101912,'2011-05-28', 22, 0, 0,0,1,79, 78.44)

>>IssCost = IssRate * IssQty<<

Aren't you trying to calculate IssRate only? Please advise.

let's try to clarify this, if this is your formula to calculate IssRate:

IssRate = Sum(RecCost) - Sum(IssCost) / sum(RecQty) - Sum(IssQty)

if we use

insert @t values(1640933, 100545, '2011-05-01', 280, 78.44, 21963.2, 0, 0, 0),
(1641900, 101204, '2011-05-10', 0, 0,0,1,79, 0),
(1642774, 101912,'2011-05-28', 0, 0,0,1,79, 0)

sum(IssCost) will always be 0, not sure why you are mixing it with the new calculation of IssRate there.

ASKER

Let me explain you row by row,

Sr#1. Purchase entry: RecRate was 79. Rate changed to 78.44, which was updated by separate query.

After the purchase entry rate change, I want to calculate wtd Avg. Rate for subsequent Issues (Material Out).
The formula for for that is IssRate = Sum(RecCost) - Sum(IssCost) / sum(RecQty) - Sum(IssQty)
I want to update this rate in a single query for all the Issue Entries on and after the purchase date till you find the next purchase entry. In this example there is one purchase entry, and two Material Issue entries.

Sr#2. Material Issue : The query should calculate the IssRate = 78.44 and also the IssCost = IssRate * IssQty = 78.44

Sr#3. Material Issue : problem is with this and subsequent rows.....

calculation for the row :
IssRate = sum(RecCost) - sum(IssQty) / sum(RecQty) - Sum(IssQty)

IssRate = 21963.20 - 78.44 / 279 = 78.44

Apply the formula row by row and you will understand for Sr#3. it is taking sum(reccost) = 79 which was the value before updating sr#2.

If sql server updates row by row, why it is taking old value of isscost 79 and not updated one 78.44.

Pls. apply the formula for each row and get the result . I want the same result in a single query.

Thanks.

you're not answering my questions. First I need to know which dataset I should use!

a) insert @t values(1640933, 100545, '2011-05-01', 280, 78.44, 21963.2, 0, 0, 0),
(1641900, 101204, '2011-05-10', 0, 0,0,1,79, 0),
(1642774, 101912,'2011-05-28', 0, 0,0,1,79, 0)

b) insert into #t values(1640933, 1, 100545, '2011-05-01', 13, 280, 78.44, 21963.2, 0, 0, 0),
(1641900, 1, 101204, '2011-05-15', 16, 0, 0,0,1,79, 78.44),
(1642774, 1, 101912,'2011-05-28', 22, 0, 0,0,1,79, 78.44)

a or b?

ASKER

Insert a)

insert @t values(1640933, 100545, '2011-05-01', 280, 78.44, 21963.2, 0, 0, 0),
(1641900, 101204, '2011-05-10', 0, 0,0,1,79, 79),
(1642774, 101912,'2011-05-28', 0, 0,0,1,79, 79)

and calculate wtd avg rate i.e issRate for 1641900 and 1642774 in one query.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

The query is returning the correct answeres you r using isscost in the formula which is 79 while the issrate in 78.44 so if you got a thing @ 78.44 & sold it @79 the price of rest of the items should drop.

ASKER

User generated image

ASKER

Pls. advice me to get the following result

User generated image

ASKER

Pls. disregard previous image, as 3rd row was wrong

User generated image

Please review your comment http:#a36010830

1) We are trying to calculate IssRate, right?

2) IssRate = Sum(reccost) - sum(isscost) / sum(recqty) - sum(issqty) correct?

3) If Issrate formula is correct, then how you end up doing this for sr#3???
21963.20 - 78.44 / 280 - 1

78.44 is the new Issrate, NOT the sum(isscost) do you realize that? Something you're not explaining correctly.

ASKER

Yes, we are trying to calculate IssRate.

The formula is also correct.

78.44 in the formula is sum(IssCost) and not the issRate. Pls check the formula...

Sum(RecCost) = 21963.20
Sum(IssCost) = 78.44 (It is calculated as soon as IssRate is updated/calculated = IssQty * IssRate)

you have to calculate sum of reccost & sum of isscost till sr#2 to use it in the formula for sr#3

Wtd AvgRate = Closing Cost / Closing Rate

If you put this in the excel sheet you will understand what I am saying.

Calculate IssRate for Sr#3 immediately after updating Sr#2 & you will get IssRate = 78.44

Same Result I want from the query.

Thanks.

IssRate-Calculation.xls

If the Isscost column is updated everytime then I would suggest you change it to a computed column.

Alter table yourtable
Alter column IssCost as (IssRate * IssQty);

Then run the update or select and check the results.

So let's try this example (untested)

Please notice the definition of the isscost column in the table creation statement.

declare @t table (
	sr int identity,
	id int,
	transid int,
	[date] date,
	recqty int,
	recrate decimal(18,6),
	reccost decimal(18,6),
	issqty int,
	issrate decimal(18,6),
	isscost as (issrate * issqty)
)

insert @t values(1640933, 100545, '2011-05-01', 280, 78.44, 21963.2, 0, 0),
(1641900, 101204, '2011-05-10', 0, 0,0,1,79),
(1642774, 101912,'2011-05-28', 0, 0,0,1,79)

select * from @t


select *, 
	(select isnull((sum(b.reccost) - sum(b.isscost)) / nullif(sum(b.RecQty) - sum(b.issqty), 0), 0)  from @t as b
	 where b.sr < a.sr) as newissrate
  from @t a 
where issqty > 0

Open in new window

oops the example should have been like this

declare @t table (
	sr int identity,
	id int,
	transid int,
	[date] date,
	recqty int,
	recrate decimal(18,6),
	reccost decimal(18,6),
	issqty int,
	issrate decimal(18,6),
	isscost as (issrate * issqty)
)

insert @t values(1640933, 100545, '2011-05-01', 280, 78.44, 21963.2, 0, 0),
(1641900, 101204, '2011-05-10', 0, 0,0,1,79),
(1642774, 101912,'2011-05-28', 0, 0,0,1,79)

select * from @t


select sr, id, transid, [date], recqty, recrate, reccost, issqty,  
	(select isnull((sum(b.reccost) - sum(b.isscost)) / nullif(sum(b.RecQty) - sum(b.issqty), 0), 0)  from @t as b
	 where b.sr < a.sr) as issrate, isscost
  from @t a 
where issqty > 0

Open in new window

ASKER

The result shows as follows :

sr id transid date recqty recrate reccost issqty issrate isscost newissrate
2 1641900 101204 5/10/2011 0 0 0 1 79 79 78.44
3 1642774 101912 5/28/2011 0 0 0 1 79 79 78.437992

ASKER

User generated image

My question is still unanswered...

once sr#2 is updated by sql query.
Why sr#3 is calculating with old issRate i.e 79? It means , this query is not updating row by row,

Once sr#2 is updated new issrate = 78.44 newIssCost = 78.44 ,
Consider sr#2 is updated and try to calculate issrate for sr#3 manually with the same forumula

Thanks

ASKER

for sr#2
Isscost in the query should be newly calculated rate * IssQty = 1 * 78.44 =78.44

ASKER

sorry...
we are just selecting the values and not actually updating it to the table.
after updating it to the table, need to check updating it to the table

>>we are just selecting the values and not actually updating it to the table.
after updating it to the table, need to check updating it to the table<<

Yes, the select might not work but try updating it

update a
set a.issrate = (select isnull((sum(b.reccost) - sum(b.isscost)) / nullif(sum(b.RecQty) - sum(b.issqty), 0), 0)  from @t as b
	 where b.sr < a.sr) 
from @t a 
where issqty > 0

Open in new window

ASKER

declare @t table (
      sr int identity,
      id int,
      transid int,
      [date] date,
      recqty int,
      recrate decimal(18,6),
      reccost decimal(18,6),
      issqty int,
      issrate decimal(18,6),
      isscost as (issrate * issqty)
)

insert @t values(1640933, 100545, '2011-05-01', 280, 78.44, 21963.2, 0, 0),
(1641900, 101204, '2011-05-10', 0, 0,0,1,79),
(1642774, 101912,'2011-05-28', 0, 0,0,1,79)

select * from @t

update a
set a.issrate = (select isnull((sum(b.reccost) - sum(b.isscost)) / nullif(sum(b.RecQty) - sum(b.issqty), 0), 0) from @t as b
       where b.sr < a.sr)
from @t a
where a.sr =2

update a
set a.issrate = (select isnull((sum(b.reccost) - sum(b.isscost)) / nullif(sum(b.RecQty) - sum(b.issqty), 0), 0) from @t as b
       where b.sr < a.sr)
from @t a
where a.sr =3

select * from @t

ASKER

two separate update queries give desired result, but how to do in one query, because it is not possible to update each row separately when you have to update so many rows.

Thanks.

Well then, I don't see anyother option but to use a cursor or a loop there to fire updates for each row individually. Something like this

declare @t table (
      sr int identity,
      id int,
      transid int,
      [date] date,
      recqty int,
      recrate decimal(18,6),
      reccost decimal(18,6),
      issqty int,
      issrate decimal(18,6),
      isscost as (issrate * issqty)
)

insert @t values(1640933, 100545, '2011-05-01', 280, 78.44, 21963.2, 0, 0),
(1641900, 101204, '2011-05-10', 0, 0,0,1,79),
(1642774, 101912,'2011-05-28', 0, 0,0,1,79)


declare @x int, @i int

set @x = (select max(sr) from @t where issqty >0)

while @i <= @x 
begin
	update a
	set a.issrate = (select isnull((sum(b.reccost) - sum(b.isscost)) / nullif(sum(b.RecQty) - sum(b.issqty), 0), 0)  from @t as b
	       where b.sr < a.sr) 
	from @t a 
	where a.sr = @i and a.issqty > 0
	
	@i = @i + 1
end

Open in new window

Yes using cursor is the best way here, as both fields are dependent on each other.