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


WITH trace AS(
  select *
  from
  (  -- dpm con row number para meses de un mismo codigo
    select 
      d.codigo, d.anyomes, d.ent, d.sal, c.stock as stock_actual,
      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
  ) tmp
)
SELECT 
    rn,
	codigo,
    stock_actual,
	anyomes,
    case when rn=1 then stock_actual else
      LAG(ent+sal,1) OVER (
          ORDER BY codigo, anyomes
      ) 
    end stock_ini,
    ent,
    sal,
    case when rn=1 then stock_actual else
      LAG(ent+sal,1) OVER (
          ORDER BY codigo, anyomes
      ) end + ent - sal as stock_fin
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

Enter Parameters

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