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( -- stock inicial del primer mes select rn, codigo, CASE WHEN rn=1 THEN stock_actual ELSE 0 END stock_actual, anyomes, ent, sal from ( -- dpm con row number para meses de un mismo codigo select ROW_NUMBER() OVER ( PARTITION BY d.codigo ORDER BY d.codigo, d.anyomes) as rn, d.codigo, d.anyomes, c.stock as stock_actual, d.ent, d.sal from #dpm_trace d left join #codigos c ON c.codigo = d.codigo ) tmp ) select * from trace