May 17, 2016 at 3:25 am
Hello,
I'm working on a commercial application based on C# and SQL Server 2012
I need to know how to update many records based on inserted records and here is my scenario
for example I have this product: (Apple and Orange) and I have 100 of Apples and 50 Oranges
Quantity is the sold amount each time
RemainQuantity is the remain amount after deduct the sold Quantity each
ID - BillID - ProductID - AddDate - Quantity - RemainQuantity
1 - 10 - 2 - 15/4/2016 - 10 - 90
2 - 10 - 2 - 16/4/2016 - 10 - 80
3 - 10 - 2 - 17/4/2016 - 10 - 70
4 - 10 - 3 - 18/4/2016 - 15 - 35
5 - 10 - 3 - 19/4/2016 - 10 - 25
6 - 10 - 2 - 15/4/2016 - 5 - 65
-------------------------------------
if I wanted to update the first record and set the Quantity to 15 it should affect its RemainQuantity and all the upcoming records RemainQuantity to be as below
ID - BillID - ProductID - AddDate - Quantity - RemainQuantity
1 - 10 - 2 - 15/4/2016 - 10 - 85
2 - 10 - 2 - 16/4/2016 - 10 - 75
3 - 10 - 2 - 17/4/2016 - 10 - 65
4 - 10 - 3 - 18/4/2016 - 15 - 35
5 - 10 - 3 - 19/4/2016 - 10 - 25
6 - 10 - 2 - 15/4/2016 - 5 - 60
------------------------------
kindly notic its all in the same BillID so when I update I send all this records again and it may have more or less records
how can I do it using a stored procedure
Regards
May 17, 2016 at 9:29 am
You don't want to store the amount remaining, you want to calculate it on the fly using the windowed version of SUM(). Of course, your data is missing the purchases, which would be required to fully implement this system.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 17, 2016 at 10:13 am
Thanks Drew for your reply,
Actually I will need it for some other reasons related to reports (Customer's requirements)
I did it and it is now working perfectly, just I have a small issue if I couldn't solve it I will post it here
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply