[RESOLVIDO] Lista de Presença - Crosstab/Pivot - SQL

Infraestrutura
#1

Eai pessoal, td bem? Tô tentando criar uma sql que seja tipo uma lista de presença:

NOME     | 01/06 | 02/06 | 03/06 | 04/06 | 05/06 | 06/06......

Fulano     true    false   true    true    true    true

Eu consegui algo parecido (com falhas), porém teria que refazer essa SQL todo mês e isso não é vantajoso… Eu até sei gerar uma lista dos dias do mês sem fazer isso manualmente, mas não sei como inserir isso na query abaixo:

Query:

SELECT * FROM crosstab(
      $$SELECT id_funcionario, data, present
        FROM   controle_frequencia
        WHERE  to_date(data, 'DD/MM/YYYY') BETWEEN '01/06/2020'::date AND '06/06/2020'::date
        ORDER  BY 1,2$$

     ,$$SELECT unnest('{01/06/2020, 02/06/2020, 03/06/2020,
					  04/06/2020, 05/06/2020, 06/06/2020}'::date[])$$)
					  
AS ct ("Funcionário" text, "01/06" bool, "02/06" bool, "03/06" bool
                     , "04/06" bool, "05/06" bool, "06/06" bool);

image
image.png701×138 4.55 KB 

*2, 3 e 4  são  ids dos funcionários

Código pra gerar os dias auto:

select dias::date from generate_series(
    date_trunc('month',now()),
    date_trunc('month',now()) + '1 month' - '1 day'::interval,
    '1 day'
) as series(dias)

image

Esse é o meu controle_frequencia:

image
image.png834×227 13.7 KB

#2

Bom, extraindo os dias da minha data eu consegui um resultado melhor, mas ainda sim teria que colocar todos os dias manualmente nas colunaas

image
esses q estão null é pq eu ainda nao preenchi

SELECT * FROM crosstab(
    $$
    SELECT
      nome, --nome do funcionario
      extract(DAY from data::date) AS dia, --extrai o dia da minha data
      present --boolean(true=presente, false=faltou)
    FROM 
	controle_frequencia
    LEFT JOIN
	funcionario ON funcionario.id = controle_frequencia.id_funcionario --join pra pegar o nome do funcionario
    WHERE 
	data::date >= Now() - Interval '90 Days' AND data::date < Now() - Interval '1 days' 
    ORDER BY 
	1,2
    $$,

    $$
       SELECT generate_series(1, 5) --dia 1 até o dia 5
    $$
) AS final_result (
  "Funcionário" text, "01/06" boolean, "02/06" boolean, "03/06" boolean, "04/06" boolean, "05/06" boolean
)

EDIT (resolvido por enquanto, se alguém achar alguma solução melhor, eu agradeço);

tirei o mês e deixei só os dias (1 - 31)

SELECT * FROM crosstab(
    $$
    SELECT
	  nome, --nome do funcionario
      extract(DAY from data::date) AS dia, --extrai o dia da minha data
      present --boolean(true=presente, false=faltou)
    FROM 
		controle_frequencia
	LEFT JOIN
		funcionario ON funcionario.id = controle_frequencia.id_funcionario --join pra pegar o nome do funcionario
    WHERE 
		data::date >= Now() - Interval '90 Days' AND data::date < Now() - Interval '1 days' 
    ORDER BY 
		1,2
    $$,
    $$
        SELECT generate_series(1, 31)
    $$
) AS final_result (
  "Funcionário" text, "01" boolean, "02" boolean, "03" boolean, "04" boolean, "05" boolean, "06" boolean, "07" boolean, "08" boolean, "09" boolean, "10" boolean, "11" boolean, "12" boolean, "13" boolean, "14" boolean, "15" boolean, "16" boolean, "17" boolean, "18" boolean, "19" boolean, "20" boolean, "21" boolean, "22" boolean, "23" boolean, "24" boolean, "25" boolean, "26" boolean, "27" boolean, "28" boolean, "29" boolean, "30" boolean, "31" boolean
)

Resultado:

image
image.png1534×170 15.4 KB

#3

to começando agora em sql, to respondendo na chance de eu aprender algo novo rs. se você pegasse a quantidade de valores null (“não preenchidos”) e dissesse se mês 1 possuir nulls ==0, create table fevereiro(1/02, 2/02,etc…), assim pra todos os anos.

#4

Não entendi muito bem, mas o problema na verdade está só nessas colunas… eu queria que gerasse automaticamente os dias, mas vou ver se crio uma view e consigo filtrar essas datas posterior, assim não tenho tanto problema em fazer desse jeito q ta agora

image

query:

SELECT 
	nome,
	data::date AS dia,
	extract(YEAR FROM data::date) AS year,
	extract(MONTH FROM data::date) AS month,
	extract(DAY FROM data::date) AS day,
	present
FROM 
	controle_frequencia
INNER JOIN 
   	funcionario ON funcionario.id = controle_frequencia.id_funcionario
--WHERE nome ILIKE 'Fulano%' --caso queira filtrar pelo nome
GROUP BY 
	nome, data, present
ORDER BY
	data;