1

This is a follow up to my original question, but anyone can answer nonetheless.

Increment each row according to condition in SQL C#

 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 ;

The code worked perfectly Ypercube don't have enough reputation to vote up or comment otherwise would have done it, I changed the 100 to a textbox value and it worked like a charm, just one new thing I want to add to this code, I tried but could not figure it out with CTE. I have one more column with Cost_Price and the price is different each time the same item is entered, like in case chicken is 350$ and then again chicken is 450$. What I want to do is calculate item_value according to the no of quantity added in the stock_out column with the price in that particular row.

+----------+-----------+----------+-----------+---------------+------------+---------------+
|  Bill No | Item_Name | Stock_in | Stock_out | stock_in_hand | Cost_Price | Total_Price   |
+----------+-----------+----------+-----------+---------------+------------+---------------+
|        1 |   chicken |      100 |        40 |            60 |        350 |         14000 |
|        2 |   chicken |       40 |         0 |            40 |        450 |             0 |
+----------+-----------+----------+-----------+---------------+------------+---------------+

Result to be obtained. If from the value of 100 from our previous code, 60 is added according to your cte code in stock_out in first row then total price should be calculated according to 60 x 350 and if 40 was added to second row than 40 x 450.

+----------+-----------+----------+-----------+---------------+------------+-------------+
|  Bill No | Item_Name | Stock_in | Stock_out | stock_in_hand | Cost_Price | Total_Price |
+----------+-----------+----------+-----------+---------------+------------+-------------+
|        1 |   chicken |      100 |       100 |             0 |        350 |     21000   |
|        2 |   chicken |       40 |        40 |             0 |        450 |      18000  |
+----------+-----------+----------+-----------+---------------+------------+-------------+

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.