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 #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 
  vi.id as proyecto,
  vi.inicio as inicio_captacion, vi.dias, (vi.inicio + vi.dias - 1) as fin_captacion,
  vic.codigo, vi.ahorro, p.cant as produccion,
  p.cant * vi.ahorro as vi
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 + vi.dias -1) )

fork query

edited feb 18 anonymous

created dec 21 jon.doe43432