Doubt when creating table with dates in postgresql

Question

I have the following table structure:

I would need to create a 3° table that would look like this:

I have the smallest and longest date in one table and in another table I have changes made, I would need to create a script that would generate a 3° table, creating the ranges of values.

Obs: A coluna id é um AUTOINCREMENTO.

If we were to turn it into words, it would look like this:

Between days 05-06-2018 until 19-06-2018 the value was 50.

Between 19-06-2018 until 21-06-2018 the value was 150.

Between the days 21-06-2018 until 25-06-2018 the value was 180.

Between 25-06-2018 until 05-07-2018 the value was 200.

I thought I’d use WHILE and variables, but I couldn’t complete the script. How could this be done? It doesn’t seem to be so complex, but I ended up failing.

Answer 1

You can solve your problem by using a Window Function calling for lead() combined with a 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;

Exit:

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

Exit:

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

Answer 2

As I mentioned in the comments, I do not see why Tabela1 seems to me only parameter of the query.

Anyway, I used only the smaller date to be displayed as the initial of the first record.

The query is quite simple: Setting Date Change to be the End Date of the record, and Start Date being the longest date of the previous record, just do a sub-select:

SELECT t.id,
       coalesce(
                  (SELECT max(x.dataAlteracao)
                   FROM tabela2 x
                   WHERE x.id < t.id),
                  (SELECT min(DATA)
                   FROM tabela1)) AS data_inicio,
       t.dataAlteracao,
       t.valorAnterior,
       t.ValorAtual
FROM tabela2 t

I put in the Sqlfiddle