row to col and previous row

0

Please login or register to vote for this query.

(click on this box to dismiss)

Genealogy and Family History Meta

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(  -- stock inicial del primer mes
  select
    codigo,
    anyomes,
    CASE WHEN rn=1 THEN stock_actual ELSE 0 END stock_actual,
    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

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results