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 #produccion
  (dt datetime, [codigo] varchar(10), cant int)
;

CREATE table #vi
  (id varchar(10), inicio datetime, dias int, ahorro int)
;

CREATE table #vi_codigos
  ( id varchar(10), codigo varchar(10) )
;

    
INSERT INTO #produccion
    (dt, codigo, cant)
VALUES
    ('2024-01-01', '01716', 10),
    ('2024-01-02', '01716', 10),
    ('2024-01-03', '01716', 10),
    ('2024-01-04', '01716', 10),
    ('2024-01-05', '01716', 10),
    ('2024-01-06', '01716', 10),
    ('2024-01-07', '01716', 10),
    ('2024-01-08', '01716', 10),
    ('2024-01-09', '01716', 10),
    ('2024-01-10', '01716', 10),
    ('2024-01-01', '01720', 10),
    ('2024-01-02', '01720', 10),
    ('2024-01-03', '01720', 10),
    ('2024-01-04', '01720', 10),
    ('2024-01-05', '01720', 10),
    ('2024-01-06', '01720', 10),
    ('2024-01-07', '01720', 10),
    ('2024-01-08', '01720', 10),
    ('2024-01-09', '01717', 10),
    ('2024-01-10', '01720', 10)
;


INSERT INTO #vi
    (id, inicio, dias, ahorro)
VALUES
    ('vi1', '2024-01-01', 3, 1),
    ('vi2', '2024-01-05', 10, 2)
;


INSERT INTO #vi_codigos
    (id, codigo)
VALUES
    ('vi1', '01716'),
    ('vi1', '01717'),
    ('vi2', '01720')
;



----------------------------

select * from #vi vi
left join #vi_codigos vic ON vic.id=vi.id
left join #produccion p ON p.codigo=vic.codigo AND p.dt between vi.inicio and vi.inicio+2

Enter Parameters

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