0
I’m learning SQL and I still have a lot to learn, but I’m trying to generate a report and I found some difficulties. I made that first consultation:
/* Aqui estou selecionando as colunas e gerando a média entre a data de entrada e saído da tramitação */
SELECT PPRO.cod_proposicao_tipo AS "ID do Subassunto"
, PPRO.pro_processo AS "Número do Processo"
, PPRO.pro_ano AS "Ano"
, PPF.prf_estagio AS "Estágio"
, PPT.prt_nome AS "Subassunto"
, PPFA.fas_nome AS "Atividade"
, DATEDIFF(MINUTE,PPF.prf_data_chegada,PPF.prf_data_saida) AS "Tempo Gasto em Minutos"
/* Aqui estou selecionando as tabelas e seus relacionamentos */
FROM prl_proposicoes AS PPRO
INNER JOIN pro_proposicoes_fases AS PPF
ON PPRO.cod_proposicao = PPF.cod_proposicao
LEFT JOIN prl_proposicoes_situacoes AS PPS
ON PPRO.cod_proposicao_situacao = PPS.cod_proposicao_situacao
INNER JOIN prl_proposicoes_tipos AS PPT
ON PPRO.cod_proposicao_tipo = PPT.cod_proposicao_tipo
INNER JOIN prl_proposicoes_tipos_grandes AS PPTG
ON PPT.cod_proposicao_tipo_grande = PPTG.cod_proposicao_tipo_grande
INNER JOIN prl_fases AS PPFA
ON PPF.cod_fase_entrada = PPFA.cod_fase
INNER JOIN prl_setores
ON PPF.cod_setor_despacho = prl_setores.cod_setor
/* Aqui estou colocanco as condições da consulta */
WHERE PPRO.pro_ano = '2020'
AND PPT.prt_area = 'ELE'
AND PPRO.pro_processo = '353'
AND PPRO.cod_proposicao_tipo = '1844010'
/* Aqui estou ordenando as atividades */
ORDER BY PPF.prf_estagio DESC
That returned me this result:
From the above result you need to generate the average time spent by "Atividade", then I made the following consultation:
WITH Consulta
AS (SELECT PPT.prt_area AS "Área", PPRO.cod_proposicao_tipo AS "ID do Subassunto"
, PPRO.pro_processo AS "Número do Processo", PPRO.pro_ano AS "Ano"
, PPF.prf_estagio AS "Estágio", PPT.prt_nome AS "Subassunto"
, PPFA.fas_nome AS "Atividade", DATEDIFF(MINUTE,PPF.prf_data_chegada,PPF.prf_data_saida) AS "Tempo"
FROM prl_proposicoes AS PPRO
INNER JOIN pro_proposicoes_fases AS PPF
ON PPRO.cod_proposicao = PPF.cod_proposicao
LEFT JOIN prl_proposicoes_situacoes AS PPS
ON PPRO.cod_proposicao_situacao = PPS.cod_proposicao_situacao
INNER JOIN prl_proposicoes_tipos AS PPT
ON PPRO.cod_proposicao_tipo = PPT.cod_proposicao_tipo
INNER JOIN prl_proposicoes_tipos_grandes AS PPTG
ON PPT.cod_proposicao_tipo_grande = PPTG.cod_proposicao_tipo_grande
INNER JOIN prl_fases AS PPFA
ON PPF.cod_fase_entrada = PPFA.cod_fase
INNER JOIN prl_setores
ON PPF.cod_setor_despacho = prl_setores.cod_setor
WHERE PPRO.pro_ano = '2020'
AND PPT.prt_area = 'ELE'
AND PPRO.cod_proposicao_tipo = '1844010')
SELECT Consulta.Atividade, AVG(Consulta.Tempo)
FROM Consulta
WHERE Consulta.[ID do Subassunto] = '1844010'
AND Consulta.Área = 'ELE'
AND Consulta.Estágio = '2'
GROUP BY Consulta.Atividade
ORDER BY Consulta.Estágio
But it is returning error, as print:
What is the meaning of this
ORDER BY Consulta.Estágioif you putAND Consulta.Estágio = '2'in its WHERE clause?– anonimo
The AND Query.Stage = '2' can disregard, it was a condition that was testing, but the ORDER BY Query.Stage is to sort by Stage/Stages of the process, but can be removed as well. But what I want is a query where I take the Subsurface (Process Type) with its respective Activities, to extract the average time spent of all processes finding which activity is consuming the most time
– Getulio Barreto Rodrigues
Note that in the query I generated was only for a specific process (nº 353 of Unpaid Leave) but the idea is to generate of all the process of "Unpaid Leave" and find the average of the activity that is consuming more time and that is consuming less time.
– Getulio Barreto Rodrigues
In the article Data analysis with SQL: averages you can find ways to calculate averages using window functions: -> https://portosql.wordpress.com/2020/10/16/parses_sql-medias
– José Diz