0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for expert genealogists and people interested in genealogy or family history

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,
    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,
    LAG(ent+sal,1) OVER (
		ORDER BY codigo, anyomes
	) stock_ini,
    ent,
    sal,
    LAG(ent+sal,1) OVER (
		ORDER BY codigo, anyomes
	) + ent - sal
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

view current version

edited dec 21 23

created dec 21 23 jon.doe43432