row to col and previous row

0

Please login or register to vote for this query.

(click on this box to dismiss)

IT Security Meta

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)
;



-- Calculamos el stock proyectado mes a mes por articulo
WITH stock_proyectado AS (  -- stock inicial del primer mes y acumulado entrada y salidas
    select 
      ROW_NUMBER() OVER ( PARTITION BY d.codigo ORDER BY d.codigo, d.anyomes) as rn,
      d.codigo, c.origen, d.anyomes, c.stock as stock_actual, d.ent, d.sal,
      sum(d.ent) over (partition by d.codigo order by d.codigo, d.anyomes) as ent_acumulada,
      sum(d.sal) over (partition by d.codigo order by d.codigo, d.anyomes) as sal_acumulada
    from #dpm_trace d
    left join #codigos c ON c.codigo = d.codigo
)
select 
  rn, codigo, origen,
  stock_actual,
  anyomes,
  case when rn=1 
    then stock_actual
    else stock_actual 
        + LAG(ent_acumulada, 1) OVER (ORDER BY codigo, rn) 
        - LAG(sal_acumulada, 1) OVER (ORDER BY codigo, rn) 
  end as stock_ini,
  ent,
  sal,
  --ent_acumulada,
  --sal_acumulada,
  --[stock_fin] = 1
  [stock_fin] = stock_actual + ent_acumulada - sal_acumulada
into #stock_proyectado
from stock_proyectado


-- Convertimos el stock proyectado a una fila por codigo
select 
  codigo, 
  origen,
  case when anyomes = 202401 then stock_ini end [m+1_ini],
  case when anyomes = 202402 then stock_ini end [m+2_ini],
  case when anyomes = 202403 then stock_ini end [m+3_ini],
  case when anyomes = 202404 then stock_ini end [m+4_ini],
  --
  case when anyomes = 202401 then ent end [m+1_ent],
  case when anyomes = 202402 then ent end [m+2_ent],
  case when anyomes = 202403 then ent end [m+3_ent],
  case when anyomes = 202404 then ent end [m+4_ent],
  --
  case when anyomes = 202401 then sal end [m+1_sal],
  case when anyomes = 202402 then sal end [m+2_sal],
  case when anyomes = 202403 then sal end [m+3_sal],
  case when anyomes = 202404 then sal end [m+4_sal],
  --
  case when anyomes = 202401 then stock_fin end [m+1_fin],
  case when anyomes = 202402 then stock_fin end [m+2_fin],
  case when anyomes = 202403 then stock_fin end [m+3_fin],
  case when anyomes = 202404 then stock_fin end [m+4_fin]
from #stock_proyectado

Enter Parameters

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