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


select 
  c.codigo,
  -- mes+1
  case when d.anyomes=202401 then c.stock end [m+1_stock_ini],
  case when d.anyomes=202401 then d.ent end [m+1_ent],
  case when d.anyomes=202401 then d.sal end [m+1_sal],
  case when d.anyomes=202401 then c.stock + d.ent - d.sal end [m+1_stock_fin],
  -- mes+2
  case when d.anyomes=202402 then c.stock end [m+1_stock_ini],
  case when d.anyomes=202402 then d.ent end [m+1_ent],
  case when d.anyomes=202402 then d.sal end [m+1_sal],
  case when d.anyomes=202402 then c.stock + d.ent - d.sal end [m+1_stock_fin]  
from #dpm_trace d
left join #codigos c ON d.codigo=c.codigo
;





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