Click here to Skip to main content
15,444,353 members
Please Sign up or sign in to vote.
1.33/5 (3 votes)
See more:
i want to subtract 40kg rice from qty column date wise like from 1st row 10 and 2ed row 25 and so on

ing_id|qty|Price|Date       |unit |name
01    |10|50    |12/12/2018 |kg   |Rice
01    |25|56    |12/14/2018 |kg   |Rice
01    |30|70    |12/20/2018 |kg   |Rice

and update the table like :

ing_id|qty|Price|Date       |unit |name
01    |00|50    |12/12/2018 |kg   |Rice
01    |00|56    |12/14/2018 |kg   |Rice
01    |15|70    |12/20/2018 |kg   |Rice

hepl!! Thanks in Advance

What I have tried:

I dont undarstand how to do!
any suggestion
or help
Updated 22-Jul-21 23:10pm
Afzaal Ahmad Zeeshan 12-Dec-18 8:09am     CRLF
1st row 10, and second row 25. Where are these conditions coming from? Most likely, you need to perform a SELECT...UPDATE query. Or a nested SELECT within the UPDATE query.
Member 14086748 12-Dec-18 9:54am    
from the database
Afzaal Ahmad Zeeshan 12-Dec-18 12:38pm    
Member 14086748 13-Dec-18 7:20am     CRLF
ing_id|qty|Price|Date |unit |name 01 |10|50 |12/12/2018 |kg |Rice 01 |25|56 |12/14/2018 |kg |Rice 01 |30|70 |12/20/2018 |kg |Rice from the table

Afzaal Ahmad Zeeshan is connect, I'm afraid.

In SQL you can aplly changes to multible rows only if the command is the same for every row! Thus applying different substractions for specific rows you'll have to define a specific command for each substraction.

The only exception would be if you could define a general equation which uses the table data as input - e.g. by using a sub-select getting the average value to subtract and then using that to apply a calculated value to each row. But for that I'm afraid you didn't give enough information to find such a way.
Share this answer
actually, 3rd qty in the 2nd table should be 25 instead of 15 for the 40kg question versus 50kg. if this is going to be an ongoing thing, create a stored procedure to walk through the rowset given sku, qty, with some error handling for over/under qty available.. or just figure out the stored procedure and code it at the VS/C# level. that's a cool request, as usual, necessity is the mother of invention. need the stored procedure. , let me know.
Share this answer
CREATE TABLE #table(ID int, Qty Decimal(11,3))
INSERT INTO #table(ID,Qty)
VALUES	(1, 1),
		(2, 5),
		(3, 5),
		(4, 10);

Declare @q int = 12;

SELECT id, Qty
       ,(@q - cumvalue1 ) as RemainingQtyForNextRow
       ,(CASE When @q - cumvalue1 > 0 THEN 0 ELSE  cumvalue1 - @q  END) as RemainingQty
       sum(Qty) over (order by id) as cumvalue1
      FROM #table t
     ) t;
Share this answer
CHill60 23-Jul-21 5:18am    
A code dump with no commentary is not a good solution - especially when the table you have created does not resemble the original post neither in schema nor content

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900