Atribuir períodos para intervalos de horários

21/08/2019

0

SQL Oracle PL SQL

Bom dia.
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

Diego

Responder

Post mais votado

23/08/2019


então acredito que a melhor forma seja utilizar stored procedure.

Emerson Nascimento

Emerson Nascimento
Responder
Gostei + 1

Mais Posts

22/08/2019

Emerson Nascimento

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, periodo
Responder
Gostei + 0

22/08/2019

Diego

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....

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'


Responder
Gostei + 0

22/08/2019

Emerson Nascimento

Você está limitado a 4000 caracteres onde? No banco de dados?
Responder
Gostei + 0

23/08/2019

Diego

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.
Responder
Gostei + 0

27/08/2019

Diego


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?
Responder
Gostei + 0

Utilizamos cookies para fornecer uma melhor experiência para nossos usuários, consulte nossa política de privacidade.

Aceitar