You can solve your problem by using Window Function
calling lead()
combined with an UNION ALL
.
Assuming your tables are something like:
CREATE TABLE tb_tabela1
(
id BIGSERIAL PRIMARY KEY,
data DATE
);
INSERT INTO
tb_tabela1 ( id, data )
VALUES
( 1, '2018-06-05' ),
( 2, '2018-07-05' );
CREATE TABLE tb_tabela2
(
id BIGSERIAL PRIMARY KEY,
dataAlteracao DATE,
valorAnterior INTEGER,
valorAtual INTEGER
);
INSERT INTO
tb_tabela2 ( id, dataAlteracao, valorAnterior, valorAtual )
VALUES
( 1, '2018-06-19', 50, 150 ),
( 2, '2018-06-21', 150, 180 ),
( 3, '2018-06-25', 180, 200 );
Your query would look like this:
(SELECT
dataAlteracao AS dataInicio,
lead(dataAlteracao,1,(SELECT max(data) FROM tb_tabela1)) OVER (ORDER BY dataAlteracao) AS dataFim,
valorAtual AS valor
FROM
tb_tabela2)
UNION ALL
(SELECT
(SELECT min(data) FROM tb_tabela1),
dataAlteracao,
valorAnterior
FROM
tb_tabela2 ORDER BY dataAlteracao LIMIT 1
) ORDER BY dataFim;
Output:
| datainicio | datafim | valor |
|------------|------------|-------|
| 2018-06-05 | 2018-06-19 | 50 |
| 2018-06-19 | 2018-06-21 | 150 |
| 2018-06-21 | 2018-06-25 | 180 |
| 2018-06-25 | 2018-07-05 | 200 |
See working in SQLFiddle.com
To insert your data into a third table, you can do something like:
CREATE TABLE tb_tabela3
(
id BIGSERIAL PRIMARY KEY,
dataInicio DATE,
dataFim DATE,
valor INTEGER
);
Entering the data:
INSERT INTO tb_tabela3 ( dataInicio, dataFim, valor ) (
(SELECT
dataAlteracao AS dataInicio,
lead(dataAlteracao,1,(SELECT max(data) FROM tb_tabela1)) OVER (ORDER BY dataAlteracao) AS dataFim,
valorAtual AS valor
FROM
tb_tabela2)
UNION ALL
(SELECT
(SELECT min(data) FROM tb_tabela1),
dataAlteracao,
valorAnterior
FROM
tb_tabela2 ORDER BY dataAlteracao LIMIT 1
));
Testing:
SELECT * FROM tb_tabela3 ORDER BY dataInicio;
Output:
| id | datainicio | datafim | valor |
|----|------------|------------|-------|
| 1 | 2018-06-05 | 2018-06-19 | 50 |
| 2 | 2018-06-19 | 2018-06-21 | 150 |
| 3 | 2018-06-21 | 2018-06-25 | 180 |
| 4 | 2018-06-25 | 2018-07-05 | 200 |
See working in SQLFiddle