Discount Present Update When Quantity Change

Question

I have table call tblInvoice. This is my table with some data.

InvoiceNo	ItemName	Quantity	CostPrice	ItemCode	DiscountPrice	DisPresent	Amount	GrossAmount	SalePrice
INV01	80= BK	10	30.00	EX80=	40.00	100.00	400.00	575.00	50.00
INV01	80= BK	5	30.00	EX80=	35.00	75.00	175.00	575.00	50.00

My client is sell same item with different price. as you can see here DiscountPrice is different but ItemName and ItemCode is same. When return product after sold I want to subtraction quantity. Its ok I already done that part. But problem is I want to update DiscountPesent after return product. its like this. Imagine I return 1 Book DiscountPrice is 40.00. Not 35.00. I want to update only first row. not both rows. I want to get DiscountPresent Like this.

SalePrice - DiscountPrice * Quantity = DiscountPresent

According to the above table. Imagine I subtraction 1 Quantity from DiscountPrice 40.00 row. now My Quantity is 9 I want to get DiscountPresent Like this.

50 - 40 = 10 * 9 = 90

I used following query for achieve this. sometimes its work as expected. but sometimes DiscoutPresent Switching with second row DiscountPeresent. After that table look like this.

InvoiceNo	ItemName	Quantity	CostPrice	ItemCode	DiscountPrice	DisPresent	Amount	GrossAmount	SalePrice
INV01	80= BK	9	30.00	EX80=	40.00	75.00	400.00	575.00	50.00
INV01	80= BK	5	30.00	EX80=	35.00	90.00	175.00	575.00	50.00

90 is come to second row. 75 is come to first row. its wrong. I used following code. sometimes its work as expected. but sometimes it switching DiscountPresent.

 UPDATE ps
SET DisPresent = ((i.SalePrice) - (i.DiscountPrice)) * (i.Quantity) 
FROM tblInvoice ps JOIN
     (SELECT i.InvoiceNo, i.DiscountPrice, i.Quantity, i.SalePrice  FROM tblInvoice i
     GROUP BY i.DiscountPrice, i.InvoiceNo, i.Quantity, i.SalePrice)i
     ON ps.InvoiceNo = i.InvoiceNo

Answer 1

looks to me you only need a simple update statement

UPDATE i
SET    DisPresent  = (i.SalePrice - i.DiscountPrice) * i.Quantity
FROM   tblInvoice i

if this is not what you wanted, please show the expected result for the sample data that you have provided