use of cursor to update item to item stock

Question

I have a bank where the stock(onhand) must be changed each time an order(docentry) is made, whether it is a confirmed order or not, for that I need to download the order quantity(Qty) with a simple subtraction operation(onhand - Qty). My question is the following, how do I carry out the stock replacement of each item every time an order is made? I can do this but only when I specify the order number but I would like to perform this operation. Follow code and result image.

Basically how it would work: the item’s Stock is at 3993, after the order "15335" the stock should upgrade to 3981 for all orders where this item exists(in case the 15681 should show in onhand 3981), then the cursor should update again when arriving at the item "AR000001" and decrease 4 of the updated stock 3981. Currently what happens: the stock drops from number 3993 for both where it is 3981 for one and 3989 for another.

--realizando inserção na tabela temporária
insert into #tmpPedidos(Docentry,qty,onhand,conta,itemcode)
(select a.docentry,b.Quantity,c.OnHand,(c.OnHand - b.Quantity),b.ItemCode
from ORDR a 
inner join RDR1 b on a.DocEntry = b.DocEntry
inner join OITW c on b.ItemCode = c.ItemCode
where
a.DocStatus = 'o' and a.CANCELED = 'n' and c.whscode = 
CASE when BPLId = 4 then '301' 
ELSE
'401'
END) --documentos com status abertos não cancelados e com estoque de numero ou 301 ou 401

declare crs cursor
for
select * from #tmpPedidos order by itemcode
update #tmpPedidos set onhand = conta where itemcode = 'ar000001' and Docentry = 15335 --aqui realizo o update indicando um pedido(que funciona)
update #tmpPedidos set conta = onhand - qty where itemcode = 'ar000001' and Docentry = 15335 -- aqui atualizo a conta indicando o pedido(que também funciona)
open crs
fetch next from crs;

I thank all those who can help, I’m starting in this part of temporary tables and cursors so I don’t know much about the use of them.

Answer 1

Friend, you can do what you need using a variable that increases.
For this you would need, first of all, to change your table and add a primary key (it may be a field id simple.

CREATE TABLE tmpPedidos(
  [id] integer identity(1,1),
  [Docentry] integer,
  [qty] integer,
  [onhand] integer,
  [conta] integer,
);

Then make an Insert leaving the account column data without filling in:

INSERT INTO tmpPedidos([Docentry],[qty],[onhand])
VALUES
    (15335, 12,3993),
    (156581,4,3993),
    (156582,2,3993),
    (156583,5,3993);

Now with this data filled in we can create a variable @conta which will update column values onhand and conta. Also note that we have created a variable @etoque_inicial to store the initial value of the stock (3993), as this will be changed and we need to turn it manually.

/*guarda o estoque inicial*/
declare @estoque_inicial int
set @estoque_inicial = (select onhand from tmpPedidos where id = 1)

/*Faz update na tabela corrigindo os dados da coluna conta*/
declare @conta int
update tmpPedidos set conta = @conta, @conta = (ISNULL(@conta,onhand)-qty),onhand = ISNULL(@conta,onhand)

/*Faz update da primeira linha voltando o valor do estoque inicial*/
update tmpPedidos set onhand = @estoque_inicial where id = 1

You can test the example above here.

Note: I think it is important to note that the easiest thing would be for you to do this inventory control in a separate table, where you would receive only the incoming and outgoing releases of each item from the stock, so your control would be much easier.

Answer 2

Here is solution using recursive CTE.

-- código #1 v2
with 
Pedidos_seq as (
SELECT *,
       seq= row_number() over (partition by itemcode order by Docentry)
  from #tmpPedidos
),
Le_Pedido as (
SELECT Docentry, qty, onhand, conta, itemcode, seq
  from Pedidos_seq
  where seq = 1
union all
SELECT Ps.Docentry, Ps.qty, LP.conta, (LP.conta - Ps.qty), PS.itemcode, Ps.seq
  from Le_Pedido as LP
       inner join Pedidos_seq as Ps on Ps.itemcode = LP.itemcode
                                      and Ps.seq = (LP.seq +1)
)
SELECT * from Le_Pedido  -- teste
/*
UPDATE P
  set onhand= LP.onhand, conta= LP.conta
  from #tmpPedidos as P
       inner join Le_Pedido as LP on LP.Docentry = P.Docentry
*/
  option (maxrecursion 5000);

I have not tested; may contain error(s).

If the query returns what you need, then delete the line ending with
-- test
and remove comments from the end of the code.

Answer 3

I share an example of creating temp table. Next I share an example of cursor...I share an example and you adjust it to your case.

Create TEMP Table

IF(OBJECT_ID('#ID_Table') IS NULL)
    BEGIN
        --IF NULL CREATE TEMP TABLE
        CREATE TABLE #ID_Table
        (
            [ID] INT
        );
    END

Removes TEMP table

--DROP TEMP TABLE IF EXISTS AFTER USAGE
IF(OBJECT_ID('#ID_Table') IS NOT NULL)
BEGIN
    DROP TABLE #ID_Table
END

Cursors Microsoft official documentation

Cursor example

NOTE: Your select to store the data must be from the TEMP table, this example is from a normal table.

Source

DECLARE @name VARCHAR(50) -- database name 
DECLARE @path VARCHAR(256) -- path for backup files 
DECLARE @fileName VARCHAR(256) -- filename for backup 
DECLARE @fileDate VARCHAR(20) -- used for file name 

SET @path = 'C:\Backup\' 

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

DECLARE db_cursor CURSOR FOR 
SELECT name 
FROM MASTER.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb') 

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  

WHILE @@FETCH_STATUS = 0  
BEGIN  
      SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 
      BACKUP DATABASE @name TO DISK = @fileName 

      FETCH NEXT FROM db_cursor INTO @name 
END 

CLOSE db_cursor  
DEALLOCATE db_cursor