Consulta ou Função para montar linha do tempo

[Gildecy Júnior Lisboa Calabró]

Preciso montar uma linha do tempo onde os períodos de VP possuem predominância sobre os períodos de DI, porém quando não houver período de VP e ainda estiver dentro do período de DI, esse período de DI deve ser contabilizado até que termine ou até que haja outro período de VP. Caso nenhum período de VP esteja sobrepondo o período de DI, segue-se a ordem normal do cadastro.

Necessito de uma consulta que exiba ou uma função que grave essa linha do tempo em uma tabela.

São 3 colunas na tabela:

|Funcão | Data Inicio | Data Fim   |

|  DI1      |01/01/2023|31/03/2023|

|  DI2      |10/01/2023|20/01/2023|

|  VP1     |10/02/2023|20/03/2023|

|  VP2     |01/04/2023|10/04/2023|

 

CADASTRO: (o que eu tenho)
DI1 = 01/01/2023 A 31/03/2023
VP1 = 10/01/2023 A 20/01/2023
VP2 = 10/02/2023 A 20/03/2023
DI2 = 01/04/2023 A 10/04/2023

LINHA DO TEMPO: (como deve ficar)
DI1 = 01/01/2023 A 09/01/2023
VP1 = 10/01/2023 A 20/01/2023
DI1 = 21/01/2023 A 09/02/2023
VP2 = 10/02/2023 A 20/03/2023
DI1 = 21/03/2023 A 31/03/2023
DI2 = 01/04/2023 A 10/04/2023

Já queimei todos os dois neurônios e não consegui chegar em uma solução.

Se alguém tiver uma solução ou uma luz de como posso fazer isso, ficarei grato.

[lulumiguel]

é só fazer um order by

 

SELECT	funcao,
		dt_inicio,
        dt_fim
FROM	tb_funcao
ORDER	BY dt_inicio;

 

[Ronivaldo Lopes]

Boa noite,

-- Criar a tabela e incluir os registros

drop table if exists tabela;

create table tabela (funcao char(3), dtinicial date, dtfinal date);

insert into tabela (funcao, dtinicial, dtfinal)
  values ('DI1','01/01/2023','31/03/2023'), ('VP1','10/01/2023','20/01/2023'),('VP2','10/02/2023','20/03/2023'),('DI2','01/04/2023','10/04/2023');

with d00 as ( select funcao, dtinicial as data, 1 as tipo, row_number() over ( order by dtinicial, funcao ) as row from tabela
              union all
              select funcao, dtfinal, 2, row_number() over ( order by dtinicial, funcao ) as row from tabela ),
     d01 as ( select *, row_number() over ( order by data, funcao ) as row1 from d00 ),
     d02 as ( select funcao, data, tipo, 1 as comando from d01 -- *
              union all
              select null, b.data-1, 2, 2 from d01 a, d01 b where a.row <> b.row and a.row1+1 = b.row1 and b.data > a.data+1 and b.tipo = 1    -- **
              union all
              select null, a.data+1, 1, 3 from d01 a, d01 b where a.row <> b.row and a.row1 = b.row1-1 and b.data > a.data+1 and a.tipo = 2 ), -- ***
     d03 as ( select *, row_number() over ( order by data ) as row from d02 ),
     d04 as ( select a.funcao, a.data as dtinicial, b.data as dtfinal from d03 a, d03 b where a.row+1 = b.row and a.tipo = 1 order by a.data, a.funcao)
  select distinct coalesce(a.funcao, b.funcao), a.dtinicial, a.dtfinal
    from d04 a
    left join tabela b on a.dtinicial between b.dtinicial and b.dtfinal and a.dtfinal between b.dtinicial and b.dtfinal
    order by a.dtinicial, coalesce(a.funcao, b.funcao);

Editado Março 16, 2023 por Ronivaldo Lopes

[lulumiguel]

se usar o order by dá certo não?

Editado Março 3, 2023 por lulumiguel

[Gildecy Júnior Lisboa Calabró]

Em 01/03/2023 em 21:05, Ronivaldo Lopes disse:

Boa noite, o que significa DI e VP, no resultado da linha do tempo a ultima linha não seria o ultimo registro original, e não DI2 ?

VP2 = 01/04/2023 A 10/04/2023

seriam cargos, DI = diretoria e VP = vice-presidencia
ocorre que o período como VP tem predominância sobre o período de DI.
DI1 seia o primeiro período dele como diretor de um departamento X e DI2 seria o período como diretor do departamento Y por exemplo. Porque ele pode ser diretor de departamentos diferentes durante a linha do tempo e também pode ter sido VP em vice-presidencias diferentes na empresa.

Por isso a necessidade de criar cortes na linha do tempo de DI para considerar o período como VP, quando acabar o período de VP volta a contar o período de DI.

[Gildecy Júnior Lisboa Calabró]

Encontrei a solução.

Fiz uma query utilizando a função generate_series() para gerar datas entre os períodos para cada um dos cargos listados.

Ordenei e fiz um case para determinar que quando houvessem datas iguais para os dois tipos de cargo, que trouxesse somente o do cargo predominante, que seria VP quando não houvesse, traz o cargo de DI.

Com isso consegui montar a linha do tempo detalhada, data a data.

Para resumir isso da forma que estava somente com data início e data fim, tive que utilizar função e armazenar os dados em variáveis para fazer inserts e updates no banco.

Segue o script que fiz, creio que ainda caiba melhorias:

--CRIANDO OU REGRIANDO A FUNÇÃO
CREATE OR REPLACE FUNCTION f_gera_linha_tempo(
	--LOCAL PARA DECLARAR PASSAGEM DE PARÂMETROS
 	)
     RETURNS bigint
     LANGUAGE 'plpgsql'
     COST 100
     VOLATILE PARALLEL UNSAFE
 AS $BODY$
  DECLARE
 	--DECLARANDO AS VARIÁVEIS
  	VFuncao		CHARACTER VARYING(100);
	VFuncaoAux	CHARACTER VARYING(100);
   	VNuFuncao			INTEGER;
	VNuFuncaoAux		INTEGER;
	i				INTEGER;
   	rec_linha		RECORD;
 	
  BEGIN
  --INICIALIZANDO AS VARIÁVEIS
  VFuncao = '';
  VFuncaoAux = '';
  VNuFuncao = 0;
  VNuFuncaoAux = 0;
  i = 0;

--DELETA OS DADOS DA TABELA PARA RECRIAR COM A FUNÇÃO
DELETE FROM tb_linha_tempo;

--INICIA LOOP
FOR rec_linha IN
--CONSULTA QUE CARREGA OS REGISTROS DO LOOP
/*	A CONSULTA IRÁ EXPANDIR OS PERÍODOS DIA A DIA USANDO GENERATE_SERIES PARA PODER ISOLAR OS 
	DIAS QUE SERÃO DESCONSIDERADOS NA SOBREPOSIÇÃO, MANTENDO SOMENTE OS DIAS QUE DEVERÃO SER CONSIDERADOS
	NA MONTAGEM DA LINHA DO TEMPO 
	
CADASTRO: (o que eu tenho)
DI1 = 01/01/2023 A 31/03/2023
VP1 = 10/01/2023 A 20/01/2023
VP2 = 10/02/2023 A 20/03/2023
DI2 = 01/04/2023 A 10/04/2023
*/
WITH funcoes AS (
SELECT	ROW_NUMBER() OVER (ORDER BY r.datas) AS sequencia,
		r.no_funcao,
		r.datas
FROM (
SELECT
	DISTINCT 
	a.no_funcao,
	CASE WHEN SUBSTRING(a.no_funcao,1,2) = 'DI' THEN 1 ELSE 2 END AS cod_funcao, 
 	generate_series(
	a.dt_inicio::DATE,
 	DATE(a.dt_fim - INTERVAL '1 DAY')::DATE,
	'1 day')::DATE AS datas
FROM	tb_funcoes AS a
) AS r
ORDER	BY datas
),
menor_funcao AS (
SELECT no_funcao, datas, MIN(cod_funcao) AS cod_funcao FROM funcoes GROUP BY 1,2
)
SELECT	dir.no_funcao,
		dir.datas
FROM	funcoes AS dir
		INNER JOIN menor_funcao AS mo ON mo.cod_funcao = dir.cod_funcao AND mo.no_funcao = dir.no_funcao AND mo.datas = dir.datas
ORDER	BY dir.datas

--COMEÇA A EXECUTAR O LOOP
 LOOP

--VARIÁVEIS PRINCIPAIS RECEBEM OS VALORES
VFuncao = rec_linha.no_funcao;
VNuFuncao = rec_linha.cod_funcao;

--SE AS VARIÁVEIS PRINCIPAIS FOREM DIFERENTES DAS AUXILIARES, ENTRA NA CONDIÇÃO
IF (VFuncao != VFuncaoAux AND VNuFuncao != VNuFuncaoAux) THEN
i = i + 1; --VARIÁVEL QUE SERVIRÁ DE ID NA TABELA
--VARIÁVEIS AUXILIARES RECEBEM O MESMO VALOR DA VARIÁVEL PRINCIPAL
VFuncaoAux = rec_linha.no_funcao;
VNuFuncaoAux = rec_linha.cod_funcao;
--INSERE LINHA NA TABELA COM DATA FIM AINDA NULA
INSERT INTO tb_linha_tempo(id_linha, no_funcao, dt_inicio,dt_fim, cod_funcao)
	VALUES (i, rec_linha.no_funcao, rec_linha.datas, null, rec_linha.cod_funcao);

ELSE
		--SE OCORRÊNCIA PRINCIPAL DIFERENTE DA AUXILIAR, ENTRA NA CONDIÇÃO
		IF (VNuFuncao != VNuFuncaoAux) THEN
		--VARIÁVEL AUXILIAR RECEBE O VALOR DA OCORRÊNCIA
		VNuFuncaoAux = rec_linha.cod_funcao;
		i = i + 1; --INCREMENTA O ID
		--INSERE LINHA NA TABELA
		INSERT INTO tb_linha_tempo(id_linha, no_funcao, cod_funcao)
			VALUES (i, rec_linha.no_funcao, rec_linha.datas, null, rec_linha.cod_funcao);
		
		ELSE
		--FICA ATUALIZANDO A DATA FIM ATÉ QUE O LOOP MUDE DE OCORRÊNCIA, A ÚLTIMA DATA A SER ATULIZADA É A QUE FECHA O PERÍODO
		UPDATE tb_linha_tempo
		SET dt_fim = rec_linha.datas
		WHERE id_linha = i;
		
		END IF; --segundo SE

END IF; --primeiro SE

END LOOP;
		--ATUALIZA A DATA FIM DAS LINHAS COM DT_FIM NULA
		--A DT_FIM FICA NULA QUANDO SÓ EXISTE UM REGISTRO DE DATA PARA A OCORRÊNCIA DO EMPREGADO
		UPDATE tb_linha_tempo
		SET dt_fim = dt_inicio
		WHERE dt_fim IS NULL;

  	RETURN 0;
  END;
 
 $BODY$;

 ALTER FUNCTION f_gera_linha_tempo()
     OWNER TO postgres;

-- SELECT f_gera_linha_tempo()
-- DROP FUNCTION f_gera_linha_tempo()
-- SELECT * FROM tb_linha_tempo ORDER BY 1
-- DELETE FROM tb_linha_tempo

 

[Ronivaldo Lopes]

.

 

Editado Março 16, 2023 por Ronivaldo Lopes