Atribuir períodos para intervalos de horários
Bom dia.
Preciso quebrar períodos de horários do dia em P1, P2, P3, P4 e P5.
Hoje tenho o seguinte sql
Porém dessa forma, que tenho uma coluna do usuário, que me traz os resultados para cada período ali do count.
Acontece que vou ter N condições, usados, não usados, cancelados, e fazer individualmente um count para cada ítem é inviável, além de ficar impossível de colocar em um report pela quantidade enorme de colunas geradas.
Eu precisaria criar um Período, nos mesmos moldes de horário que criei, mas utilizar ele como base para os outros ítens:
Exemplo de resultado:
Período |Disponíveis | Usados | Cancelados | Bloqueados| Livres | Em viagem
P1 | 20 | 8 | 3 | 5 |4 | 0
P2 | 10 | 5 | 0 | 5 | 0 | 0
E assim sucessivamente. Isso não considerando o usuário.
Eu precisaria que, para cada usuário, eu conseguisse montar a "tabela".
Da forma como está meu SQL, que ele cria uma linha para cada usuário, só me resta adicionar o período para cada situação, no caso, usadosp1,p2,p3, canceladosp1,canceladosp2.
Da forma que está construído o sql, ele via varrer todos os usuários da tabela agenda_consulta, que é o esperado.
O desafio é Conseguir pegar o usuário, dividir os resultados por períodos conforme o esboço gráfico acima, e depois partir para o próximo usuário.
A questão da divisão do usuário eu até conseguiria contornar no report, mas precisaria criar os períodos como no gráfico. Alguma luz?
Preciso quebrar períodos de horários do dia em P1, P2, P3, P4 e P5.
Hoje tenho o seguinte sql
SELECT
t.usuario, t.disponiveis, t.usados, t.usadosp1,t.usadosp2,t.usadosp3,t.usadosp4,t.usadosp5
FROM (
SELECT
obter_desc_agenda(cd_agenda) usuario,
count(1) disponiveis,
count(case when ie_status_agenda in ('E','L','N') then 1 else null end) usados,
count(case when ie_status_agenda in ('E','L','N') and to_char (dt_agenda, 'HH24:MI:SS') between ('07:00:00') and ('08:59:59') then 1 else null end) usadosP1,
count(case when ie_status_agenda in ('E','L','N') and to_char (dt_agenda, 'HH24:MI:SS') between ('09:00:00') and ('11:59:59') then 1 else null end) usadosP2,
count(case when ie_status_agenda in ('E','L','N') and to_char (dt_agenda, 'HH24:MI:SS') between ('12:00:00') and ('13:59:59') then 1 else null end) usadosP3,
count(case when ie_status_agenda in ('E','L','N') and to_char (dt_agenda, 'HH24:MI:SS') between ('14:00:00') and ('17:59:59') then 1 else null end) usadosP4,
count(case when ie_status_agenda in ('E','L','N') and to_char (dt_agenda, 'HH24:MI:SS') between ('18:00:00') and ('22:00:00') then 1 else null end) usadosP5
FROM
agenda_consulta
WHERE
trunc(dt_agenda, 'dd') between :dtinicio and :dtfim
GROUP BY
obter_desc_agenda(cd_agenda)
order by usuario asc
) t
Porém dessa forma, que tenho uma coluna do usuário, que me traz os resultados para cada período ali do count.
Acontece que vou ter N condições, usados, não usados, cancelados, e fazer individualmente um count para cada ítem é inviável, além de ficar impossível de colocar em um report pela quantidade enorme de colunas geradas.
Eu precisaria criar um Período, nos mesmos moldes de horário que criei, mas utilizar ele como base para os outros ítens:
Exemplo de resultado:
Período |Disponíveis | Usados | Cancelados | Bloqueados| Livres | Em viagem
P1 | 20 | 8 | 3 | 5 |4 | 0
P2 | 10 | 5 | 0 | 5 | 0 | 0
E assim sucessivamente. Isso não considerando o usuário.
Eu precisaria que, para cada usuário, eu conseguisse montar a "tabela".
Da forma como está meu SQL, que ele cria uma linha para cada usuário, só me resta adicionar o período para cada situação, no caso, usadosp1,p2,p3, canceladosp1,canceladosp2.
Da forma que está construído o sql, ele via varrer todos os usuários da tabela agenda_consulta, que é o esperado.
O desafio é Conseguir pegar o usuário, dividir os resultados por períodos conforme o esboço gráfico acima, e depois partir para o próximo usuário.
A questão da divisão do usuário eu até conseguiria contornar no report, mas precisaria criar os períodos como no gráfico. Alguma luz?
Diego
Curtidas 0
Melhor post
Emerson Nascimento
23/08/2019
então acredito que a melhor forma seja utilizar stored procedure.
GOSTEI 1
Mais Respostas
Emerson Nascimento
21/08/2019
pensei aqui se você não pode usar union pra isso.
SELECT
t.usuario, t.periodo, t.disponiveis, t.usados
FROM (
SELECT
obter_desc_agenda(cd_agenda) usuario,
'P1' periodo,
count(1) disponiveis,
count(case when ie_status_agenda in ('E','L','N') then 1 else null end) usados,
FROM
agenda_consulta
WHERE
trunc(dt_agenda, 'dd') between :dtinicio and :dtfim
and to_char (dt_agenda, 'HH24:MI:SS') between ('07:00:00') and ('08:59:59')
GROUP BY
1
UNION ALL
SELECT
obter_desc_agenda(cd_agenda) usuario,
'P2' periodo,
count(1) disponiveis,
count(case when ie_status_agenda in ('E','L','N') then 1 else null end) usados,
FROM
agenda_consulta
WHERE
trunc(dt_agenda, 'dd') between :dtinicio and :dtfim
and to_char (dt_agenda, 'HH24:MI:SS') between ('09:00:00') and ('11:59:59')
GROUP BY
1
UNION ALL
SELECT
obter_desc_agenda(cd_agenda) usuario,
'P3' periodo,
count(1) disponiveis,
count(case when ie_status_agenda in ('E','L','N') then 1 else null end) usados,
FROM
agenda_consulta
WHERE
trunc(dt_agenda, 'dd') between :dtinicio and :dtfim
and to_char (dt_agenda, 'HH24:MI:SS') between ('12:00:00') and ('13:59:59')
GROUP BY
1
UNION ALL
SELECT
obter_desc_agenda(cd_agenda) usuario,
'P4' periodo,
count(1) disponiveis,
count(case when ie_status_agenda in ('E','L','N') then 1 else null end) usados,
FROM
agenda_consulta
WHERE
trunc(dt_agenda, 'dd') between :dtinicio and :dtfim
and to_char (dt_agenda, 'HH24:MI:SS') between ('14:00:00') and ('17:59:59')
GROUP BY
1
UNION ALL
SELECT
obter_desc_agenda(cd_agenda) usuario,
'P5' periodo,
count(1) disponiveis,
count(case when ie_status_agenda in ('E','L','N') then 1 else null end) usados,
FROM
agenda_consulta
WHERE
trunc(dt_agenda, 'dd') between :dtinicio and :dtfim
and to_char (dt_agenda, 'HH24:MI:SS') between ('18:00:00') and ('22:00:00')
GROUP BY
1
) t
ORDER BY usuario, periodoGOSTEI 0
Diego
21/08/2019
Bom dia Emerson, muito obrigado pela atenção.
Eu fiz com UNION ALL, porém aí o código fica muito grande, e depois do P5 eu tenho o 'Total'. Como esse sql vai ser armazenado em uma tabela para ser executado em um relatório, tenho o limite de 4000 caracteres...
No meu caso atual, já está com 5547 caracteres porém vou precisar fazer mais counts, e cada count é x6 (P1,P2,P3,P4,P5,Total)
Segue o código que está funcionando, porém com excesso de caracteres....
Eu fiz com UNION ALL, porém aí o código fica muito grande, e depois do P5 eu tenho o 'Total'. Como esse sql vai ser armazenado em uma tabela para ser executado em um relatório, tenho o limite de 4000 caracteres...
No meu caso atual, já está com 5547 caracteres porém vou precisar fazer mais counts, e cada count é x6 (P1,P2,P3,P4,P5,Total)
Segue o código que está funcionando, porém com excesso de caracteres....
SELECT 'P1' p1,
count(case when a.ie_status_agenda <> 'C' then 1 else null end) disponiveis,
count(case when a.ie_status_agenda in ('E','L','N') then 1 else null end) usados,
count(case when a.ie_status_agenda in ('I') then 1 else null end) faltas,
count(case when a.ie_status_agenda in ('C') then 1 else null end) cancelamentos,
count(case when upper(substr(a.NM_PACIENTE,1,20)) IN ('BLOQUEADO (SPA)','BLOQUEADO (REFORÇO S') then 1 else null end) pa,
count(case when upper(substr(a.NM_PACIENTE,1,12)) IN ('BLOQUEADO ()') then 1 else null end) bloqueadosemmotivo
FROM
agenda_consulta a left join agenda b on a.cd_agenda = b.cd_agenda
WHERE
trunc(a.dt_agenda, 'dd') between :dt_inicial and :dt_final
AND to_char(a.dt_agenda, 'HH24:MI:SS') between ('07:00:00') and ('08:59:59')
and (b.cd_pessoa_fisica = :cd_medico_par)
GROUP BY
'P1'
UNION ALL
SELECT 'P2' p2,
count(case when a.ie_status_agenda <> 'C' then 1 else null end) disponiveis,
count(case when a.ie_status_agenda in ('E','L','N') then 1 else null end) usados,
count(case when a.ie_status_agenda in ('I') then 1 else null end) faltas,
count(case when a.ie_status_agenda in ('C') then 1 else null end) cancelamentos,
count(case when upper(substr(a.NM_PACIENTE,1,20)) IN ('BLOQUEADO (SPA)','BLOQUEADO (REFORÇO S') then 1 else null end) pa,
count(case when upper(substr(a.NM_PACIENTE,1,12)) IN ('BLOQUEADO ()') then 1 else null end) bloqueadosemmotivo
FROM
agenda_consulta a left join agenda b on a.cd_agenda = b.cd_agenda
WHERE
trunc(a.dt_agenda, 'dd') between :dt_inicial and :dt_final
AND to_char (a.dt_agenda, 'HH24:MI:SS') between ('09:00:00') and ('11:59:59')
and (b.cd_pessoa_fisica = :cd_medico_par)
GROUP BY
'P2'
UNION ALL
SELECT 'P3' p3,
count(case when a.ie_status_agenda <> 'C' then 1 else null end) disponiveis,
count(case when a.ie_status_agenda in ('E','L','N') then 1 else null end) usados,
count(case when a.ie_status_agenda in ('I') then 1 else null end) faltas,
count(case when a.ie_status_agenda in ('C') then 1 else null end) cancelamentos,
count(case when upper(substr(a.NM_PACIENTE,1,20)) IN ('BLOQUEADO (SPA)','BLOQUEADO (REFORÇO S') then 1 else null end) pa,
count(case when upper(substr(a.NM_PACIENTE,1,12)) IN ('BLOQUEADO ()') then 1 else null end) bloqueadosemmotivo
FROM
agenda_consulta a left join agenda b on a.cd_agenda = b.cd_agenda
WHERE
trunc(a.dt_agenda, 'dd') between :dt_inicial and :dt_final
and to_char (a.dt_agenda, 'HH24:MI:SS') between ('12:00:00') and ('13:59:59')
and (b.cd_pessoa_fisica = :cd_medico_par)
GROUP BY
'P3'
UNION ALL
SELECT 'P4' p4,
count(case when a.ie_status_agenda <> 'C' then 1 else null end) disponiveis,
count(case when a.ie_status_agenda in ('E','L','N') then 1 else null end) usados,
count(case when a.ie_status_agenda in ('I') then 1 else null end) faltas,
count(case when a.ie_status_agenda in ('C') then 1 else null end) cancelamentos,
count(case when upper(substr(a.NM_PACIENTE,1,20)) IN ('BLOQUEADO (SPA)','BLOQUEADO (REFORÇO S') then 1 else null end) pa,
count(case when upper(substr(a.NM_PACIENTE,1,12)) IN ('BLOQUEADO ()') then 1 else null end) bloqueadosemmotivo
FROM
agenda_consulta a left join agenda b on a.cd_agenda = b.cd_agenda
WHERE
trunc(a.dt_agenda, 'dd') between :dt_inicial and :dt_final
and to_char (a.dt_agenda, 'HH24:MI:SS') between ('14:00:00') and ('17:59:59')
and (b.cd_pessoa_fisica = :cd_medico_par)
GROUP BY
'P4'
UNION ALL
SELECT 'P5' p5,
count(case when a.ie_status_agenda <> 'C' then 1 else null end) disponiveis,
count(case when a.ie_status_agenda in ('E','L','N') then 1 else null end) usados,
count(case when a.ie_status_agenda in ('I') then 1 else null end) faltas,
count(case when a.ie_status_agenda in ('C') then 1 else null end) cancelamentos,
count(case when upper(substr(a.NM_PACIENTE,1,20)) IN ('BLOQUEADO (SPA)','BLOQUEADO (REFORÇO S') then 1 else null end) pa,
count(case when upper(substr(a.NM_PACIENTE,1,12)) IN ('BLOQUEADO ()') then 1 else null end) bloqueadosemmotivo
FROM
agenda_consulta a left join agenda b on a.cd_agenda = b.cd_agenda
WHERE
trunc(a.dt_agenda, 'dd') between :dt_inicial and :dt_final
and to_char (a.dt_agenda, 'HH24:MI:SS') between ('18:00:00') and ('22:00:00')
and (b.cd_pessoa_fisica = :cd_medico_par)
GROUP BY
'P5'
UNION ALL
SELECT 'Total' total,
count(case when a.ie_status_agenda <> 'C' then 1 else null end) disponiveis,
count(case when a.ie_status_agenda in ('E','L','N') then 1 else null end) usados,
count(case when a.ie_status_agenda in ('I') then 1 else null end) faltas,
count(case when a.ie_status_agenda in ('C') then 1 else null end) cancelamentos,
count(case when upper(substr(a.NM_PACIENTE,1,20)) IN ('BLOQUEADO (SPA)','BLOQUEAADO (REFORÇO S') then 1 else null end) pa,
count(case when upper(substr(a.NM_PACIENTE,1,12)) IN ('BLOQUEADO ()') then 1 else null end) bloqueadosemmotivo
FROM
agenda_consulta a left join agenda b on a.cd_agenda = b.cd_agenda
WHERE
trunc(a.dt_agenda, 'dd') between :dt_inicial and :dt_final
and (b.cd_pessoa_fisica = :cd_medico_par)
GROUP BY
'Total'
GOSTEI 0
Emerson Nascimento
21/08/2019
Você está limitado a 4000 caracteres onde? No banco de dados?
GOSTEI 0
Diego
21/08/2019
Você está limitado a 4000 caracteres onde? No banco de dados?
Então, o sql é inserido dentro de um campo varchar2(4000), dentro de uma estrutura para criar/executar os relatórios. Em ambiente de testes, habilitei o MAX_STRING_SIZE=extended no Oracle, e setei o varchar2(32767), porém não posso deliberadamente fazer isso em produção.
Diante disso, ou uso uma lógica que não utilize UNIONS (o que faz com que as instruções se repitam), ou vou precisar quebrar as coisas em functions, views, etc, para no sql propriamente dito somente fazer a chamada.
GOSTEI 0
Diego
21/08/2019
então acredito que a melhor forma seja utilizar stored procedure.
Acabei criando uma view...
Agora, depois de finalizado vi que ficaram muitas colunas, porque dentro de cada período P foram adicionadas várias linhas.
Pensei então em "transformar" as colunas em linhas, de forma que:
A 1a coluna fosse o nome do usuário, a 2a P1, a 3a P2, a 4a P3, e assim sucessivamente, porém como usei no exemplo anterior, a coluna viraria linha:
count(case when a.ie_status_agenda <> 'C' then 1 else null end) disponiveis,
count(case when a.ie_status_agenda in ('E','L','N') then 1 else null end) usados,
count(case when a.ie_status_agenda in ('I') then 1 else null end) faltas,
count(case when a.ie_status_agenda in ('C') then 1 else null end) cancelamentos,Então seria Usuario | P1 | P2 | P3 | P4 | P5
João
disponiveis |disponiveisnop1|disponiveisnop2|disponiveisnop3|
usados | usadosnop1|usadosnop2|usadosnop3|
faltas .... etc
E em seguida, ler o próximo usuário, repetindo os mesmos critérios de disponíveis, usados, faltas em linhas com os períodos em colunas.
Basicamente, o inverso que fiz inicialmente. Dei uma pesquisada e vi sobre o PIVOT e UNPIVOT, mas não me pareceu muito prático a princípio para casar com o Case. Alguma alternativa?
GOSTEI 0