Q&A about the site for information security professionals
CREATE table #codigos ([codigo] varchar(10), [origen] varchar(10), stock int) ; CREATE table #dpm_trace ([codigo] varchar(10), anyomes int, ent int, sal int) ; INSERT INTO #codigos (codigo, origen, stock) VALUES ('01716', 'interno', 1000), ('001', 'externo', 10), ('002', 'interno', 20), ('003', 'compras', 0) ; INSERT INTO #dpm_trace (codigo, anyomes, ent, sal) VALUES ('01716', 202401, 10, 20), ('01716', 202402, 30, 0), ('01716', 202403, 0, 50), ('01716', 202404, 100, 200), ('001', 202401, 0, 0), ('001', 202402, 30, 0), ('001', 202403, 20, 20), ('001', 202404, 0, 10) ; WITH trace AS( select d.codigo, d.anyomes, d.ent, d.sal, c.stock as stock_actual, ROW_NUMBER() OVER ( PARTITION BY d.codigo ORDER BY d.codigo, d.anyomes) as rn from #dpm_trace d left join #codigos c ON c.codigo = d.codigo ) SELECT rn, codigo, anyomes, case when rn=1 then stock_actual else LAG(ent+sal,1) OVER ( ORDER BY codigo, anyomes ) end stock_ini, ent, sal, LAG(ent+sal,1) OVER ( ORDER BY codigo, anyomes ) + ent - sal as stock_fin FROM trace --select -- max(case when columnname = 'FirstName' then value end) Firstname, -- max(case when columnname = 'Amount' then value end) Amount, -- max(case when columnname = 'PostalCode' then value end) PostalCode, -- max(case when columnname = 'LastName' then value end) LastName, -- max(case when columnname = 'AccountNumber' then value end) AccountNumber --from #yourtable