Q&A about the site for administrators, end users, developers and designers for Craft CMS
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 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) )