Sql-server – Increment each row according to condition in SQL C#

sql serversql server 2014t-sql

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
5          chicken       60        10           50
6          chicken       100       20           80
3          fish          100       30           70
4          fish          20        20            0

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#.

C# code SQL Query

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;

Any help would be greatly appreciated.

P.S I know about the sql injection and I will change my sql queries to parameterized queries.

The 100 is a example value obtained from user via textbox, how do i implement code to receive value from textbox.

Best Answer

You can use a window function to calculate the rolling sum (of stock_in_hand) in a derived table or a CTE and then use that to update the table accordingly:

WITH upd AS
  ( SELECT 
        [Bill No],
        Item_Name,
        Stock_in,
        Stock_out,
        stock_in_hand,
        sum_stock = COALESCE(
            SUM(stock_in_hand) OVER (ORDER BY Item_Name, [Bill No]
                                     ROWS BETWEEN UNBOUNDED PRECEDING
                                              AND 1 PRECEDING)
            , 0) 
    FROM
        stock_recieve
  )
UPDATE upd
SET Stock_out = Stock_out
        + CASE WHEN 100 - sum_stock >= stock_in_hand
              THEN stock_in_hand
              ELSE 100 - sum_stock
          END,
    stock_in_hand = stock_in_hand
        - CASE WHEN 100 - sum_stock >= stock_in_hand
              THEN stock_in_hand
              ELSE 100 - sum_stock
          END 
WHERE sum_stock < 100 ;
Related Question