0

Please login or register to vote for this query.

(click on this box to dismiss)

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 y acumulado entrada y salidas
  select
    rn, 
    codigo,
    CASE WHEN rn=1 THEN stock_actual ELSE 0 END stock_actual,
    anyomes,
    ent,
    sal,
    sum(ent) over (partition by codigo order by codigo, rn) as ent_acumulada,
    sum(sal) over (partition by codigo order by codigo, rn) as sal_acumulada
  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 
  rn,
  codigo,
  stock_actual,
  anyomes,
  case when rn=1 
    then stock_actual
    else LAG( stock_actual + ent_acumulada + sal_acumulada,1) OVER (ORDER BY codigo, anyomes)
  end as stock_ini,
  ent,
  sal,
  ent_acumulada,
  sal_acumulada,
  --[stock_fin] = 1
  [stock_fin] = stock_actual + ent_acumulada - sal_acumulada
from trace

view current version

edited dec 21 23

created dec 21 23 jon.doe43432