How do I increment each row according to condition in SQL C#

Question

0.00/5 (No votes)

See more:

, +

I have a value of 100 from a textBox and I want to increment that value in the stock_out column but only till stock_out column is equal to stock_in column, if first row value is 40 than first row stock_out should be incremented by 10 from 100 than the remaining value from 100(90) should be incremented in the row below it and then row below it till needed (till 100 is 0). Items are ordered by Item_Name and Bill No asc.

Bill No   Item_Name   Stock_in  Stock_out  stock_in_hand
1          chicken       50        40           10
2          chicken       40         0           40
3          fish          100       30           70
4          fish          20        20            0
5          chicken       60        10           50
6          chicken       100       20           80

The result to be obtained.

Bill No   Item_Name   Stock_in  Stock_out  stock_in_hand
1          chicken       50        50           0
2          chicken       40        40           0
5          chicken       60        60           0
6          chicken       100       20          80
3          fish          100       30          70
4          fish          20        20           0

Here is the code that I have been able to do so far, but it needs a lot of work and I do not have expertise in cte or any complicated c# coding. I am using SQL Server 2014 and c#.

P.S I know about the sql injection and I will change my sql queries to parameterized queries.
Any help would be greatly appreciated.

What I have tried:

C# code SQL Query

SQL

select * 
from stock_recieve 
where Item_Name like'" + comboBoxitem.Text + "' 
where Stock_out < Stock_in 
order by [Bill No] asc;

update stock_recieve 
     set Stock_out = Stock_out+" + qty + "
where Stock_out < Stock_in;

Posted 9-Jul-18 4:32am

Member 13423880

Updated 12-Jul-18 2:00am

CHill60

v2

Add a Solution

Comments

j snooze 9-Jul-18 17:28pm

Now your query will need to have a bit more logic incase the exact amount you are trying to distribute is not equal to the exact running total. But the following sample I used in a temp table will basically bring back results with the last column being a running total of how much stock you need to fill that bill number. You would be able to select just the rows that you need to update and with some c# looping calculate how much each row gets. To get the idea of what this query does, remove the where clause and run it to see that the "numberneeded" is a running total of how much is needed to make the stockout = stockin. (I just did a temp table with a similar name and similar column names, modify to your specifications).

Select *
From (Select billno,item,stockin,stockout,stockinhand,sum(stockin-stockout) over (order by item,billno) as numberneeded
from #stock_recieve) a
Where numberneeded <= 100

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

mudgilsks · Answer 1 · 2018-07-12T02:00:00

--Please Try This, replace your table and column name if not matching
DECLARE @Counter as int=1
	Declare @Stock as int=100

declare @StockRecieved as table(Sr int, BillNo int)
insert into @StockRecieved
SELECT ROW_NUMBER() over(order by [Item_Name], [Bill No]) Sr,[Bill No]     
  FROM [dbo].[stock_recieve] 

	WHILE(@Stock>0)
		BEGIN
		declare @DiffInOut int
		declare @StockToBeUpdated int
		declare @BILLNo int
select @BILLNo= BillNo from @StockRecieved where Sr=@Counter
select  @DiffInOut=[Stock_in]-[Stock_out] FROM [dbo].[stock_recieve] where [Bill No]=@BILLNo

		if (@Stock>=@DiffInOut)
			BEGIN
				SET @StockToBeUpdated=@DiffInOut
				SET @Stock=@Stock-@DiffInOut
			END
		ELSE
			BEGIN
			 SET @StockToBeUpdated=@Stock
			 SET @Stock=0
			END

		UPDATE [stock_recieve] set [Stock_out]=[Stock_out]+@StockToBeUpdated where [Bill No]=@BILLNo
		
		SET @Counter=@Counter+1;
		END

--To confirm
		SELECT *   FROM [dbo].[stock_recieve] order by [Item_Name], [Bill No]