Ir para conteúdo
Fórum Script Brasil
  • 0

Query - Somando os dias da semana em cada mes


esouzanu

Pergunta

Caros amigos, estou com uma duvida para gerar um script.

O intuito é somar os horario de todos os dias da semana.
Mas só consigo fazer para a primeira semana, Podem me ajudar. abaixo meu script.

Caros amigos, estou com uma duvida para gerar um script.

O intuito é somar os horário de todos os dias da semana.
Mas só consigo fazer para a primeira semana, Podem me ajudar. abaixo meu script.

 

select 
             agen.short_desc as nome_agenda_curto,
             agen.agenda_key as codigo_da_agenda,
             agesch.agenda_schedule_key as codigo_horario,
             case when agesch.monday then ((to_timestamp(to_char(agesch.final_time,'HH24:MI:SS'),'HH24:MI:SS')) - to_timestamp(to_char(agesch.init_time,'HH24:MI:SS'),'HH24:MI:SS')) else null end as monday_2,
             case when agesch.tuesday then ((to_timestamp(to_char(agesch.final_time,'HH24:MI:SS'),'HH24:MI:SS')) - to_timestamp(to_char(agesch.init_time,'HH24:MI:SS'),'HH24:MI:SS')) else null end as tuesday_2,
             case when agesch.wednesday then ((to_timestamp(to_char(agesch.final_time,'HH24:MI:SS'),'HH24:MI:SS')) - to_timestamp(to_char(agesch.init_time,'HH24:MI:SS'),'HH24:MI:SS')) else null end as wednesday_2,
             case when agesch.thursday then ((to_timestamp(to_char(agesch.final_time,'HH24:MI:SS'),'HH24:MI:SS')) - to_timestamp(to_char(agesch.init_time,'HH24:MI:SS'),'HH24:MI:SS')) else null end as thursday_2,
             case when agesch.sunday then ((to_timestamp(to_char(agesch.final_time,'HH24:MI:SS'),'HH24:MI:SS')) - to_timestamp(to_char(agesch.init_time,'HH24:MI:SS'),'HH24:MI:SS')) else null end as sunday_2,
             case when agesch.saturday then ((to_timestamp(to_char(agesch.final_time,'HH24:MI:SS'),'HH24:MI:SS')) - to_timestamp(to_char(agesch.init_time,'HH24:MI:SS'),'HH24:MI:SS')) else null end as saturday_2,
             case when agesch.friday then ((to_timestamp(to_char(agesch.final_time,'HH24:MI:SS'),'HH24:MI:SS')) - to_timestamp(to_char(agesch.init_time,'HH24:MI:SS'),'HH24:MI:SS')) else null end as friday_2,
                     
             --SOMANDO           
             case when agesch.monday then ((to_timestamp(to_char(agesch.final_time,'HH24:MI:SS'),'HH24:MI:SS')) - to_timestamp(to_char(agesch.init_time,'HH24:MI:SS'),'HH24:MI:SS')) else '00:00:00' end +
             case when agesch.tuesday then ((to_timestamp(to_char(agesch.final_time,'HH24:MI:SS'),'HH24:MI:SS')) - to_timestamp(to_char(agesch.init_time,'HH24:MI:SS'),'HH24:MI:SS')) else '00:00:00' end  +
             case when agesch.wednesday then ((to_timestamp(to_char(agesch.final_time,'HH24:MI:SS'),'HH24:MI:SS')) - to_timestamp(to_char(agesch.init_time,'HH24:MI:SS'),'HH24:MI:SS')) else '00:00:00' end +
             case when agesch.thursday then ((to_timestamp(to_char(agesch.final_time,'HH24:MI:SS'),'HH24:MI:SS')) - to_timestamp(to_char(agesch.init_time,'HH24:MI:SS'),'HH24:MI:SS')) else '00:00:00' end +
             case when agesch.sunday then ((to_timestamp(to_char(agesch.final_time,'HH24:MI:SS'),'HH24:MI:SS')) - to_timestamp(to_char(agesch.init_time,'HH24:MI:SS'),'HH24:MI:SS')) else '00:00:00' end +
             case when agesch.saturday then ((to_timestamp(to_char(agesch.final_time,'HH24:MI:SS'),'HH24:MI:SS')) - to_timestamp(to_char(agesch.init_time,'HH24:MI:SS'),'HH24:MI:SS')) else '00:00:00' end +
             case when agesch.friday then ((to_timestamp(to_char(agesch.final_time,'HH24:MI:SS'),'HH24:MI:SS')) - to_timestamp(to_char(agesch.init_time,'HH24:MI:SS'),'HH24:MI:SS')) else '00:00:00' end as TOTAL,
             ((to_timestamp(to_char(agesch.final_time,'HH24:MI:SS'),'HH24:MI:SS')) - to_timestamp(to_char(agesch.init_time,'HH24:MI:SS'),'HH24:MI:SS')) as TEMPO_HORARIO,
             case when agesch.monday then ((to_timestamp(to_char(agelock.final_date_time,'HH24:MI:SS'),'HH24:MI:SS')) - to_timestamp(to_char(agelock.init_date_time,'HH24:MI:SS'),'HH24:MI:SS')) else '00:00:00' end +
             case when agesch.tuesday then ((to_timestamp(to_char(agelock.final_date_time,'HH24:MI:SS'),'HH24:MI:SS')) - to_timestamp(to_char(agelock.init_date_time,'HH24:MI:SS'),'HH24:MI:SS')) else '00:00:00' end  +
             case when agesch.wednesday then ((to_timestamp(to_char(agelock.final_date_time,'HH24:MI:SS'),'HH24:MI:SS')) - to_timestamp(to_char(agelock.init_date_time,'HH24:MI:SS'),'HH24:MI:SS')) else '00:00:00' end +
             case when agesch.thursday then ((to_timestamp(to_char(agelock.final_date_time,'HH24:MI:SS'),'HH24:MI:SS')) - to_timestamp(to_char(agelock.init_date_time,'HH24:MI:SS'),'HH24:MI:SS')) else '00:00:00' end +
             case when agesch.sunday then ((to_timestamp(to_char(agelock.final_date_time,'HH24:MI:SS'),'HH24:MI:SS')) - to_timestamp(to_char(agelock.init_date_time,'HH24:MI:SS'),'HH24:MI:SS')) else '00:00:00' end +
             case when agesch.saturday then ((to_timestamp(to_char(agelock.final_date_time,'HH24:MI:SS'),'HH24:MI:SS')) - to_timestamp(to_char(agelock.init_date_time,'HH24:MI:SS'),'HH24:MI:SS')) else '00:00:00' end +
             case when agesch.friday then ((to_timestamp(to_char(agelock.final_date_time,'HH24:MI:SS'),'HH24:MI:SS')) - to_timestamp(to_char(agelock.init_date_time,'HH24:MI:SS'),'HH24:MI:SS')) else '00:00:00' end as TOTAL_BLOCK,
                                --  (TOTAL_HORARIO - TOTAL_BLOCK)  
           CASE WHEN agelock.agenda_schedule_key=agesch.agenda_schedule_key 
                then(   
             ((case when agesch.monday then ((to_timestamp(to_char(agesch.final_time,'HH24:MI:SS'),'HH24:MI:SS')) - to_timestamp(to_char(agesch.init_time,'HH24:MI:SS'),'HH24:MI:SS'))     else '00:00:00' end +
             case when agesch.tuesday then ((to_timestamp(to_char(agesch.final_time,'HH24:MI:SS'),'HH24:MI:SS')) - to_timestamp(to_char(agesch.init_time,'HH24:MI:SS'),'HH24:MI:SS')) else '00:00:00' end  +
             case when agesch.wednesday then ((to_timestamp(to_char(agesch.final_time,'HH24:MI:SS'),'HH24:MI:SS')) - to_timestamp(to_char(agesch.init_time,'HH24:MI:SS'),'HH24:MI:SS')) else '00:00:00' end +
             case when agesch.thursday then ((to_timestamp(to_char(agesch.final_time,'HH24:MI:SS'),'HH24:MI:SS')) - to_timestamp(to_char(agesch.init_time,'HH24:MI:SS'),'HH24:MI:SS')) else '00:00:00' end +
             case when agesch.sunday then ((to_timestamp(to_char(agesch.final_time,'HH24:MI:SS'),'HH24:MI:SS')) - to_timestamp(to_char(agesch.init_time,'HH24:MI:SS'),'HH24:MI:SS')) else '00:00:00' end +
             case when agesch.saturday then ((to_timestamp(to_char(agesch.final_time,'HH24:MI:SS'),'HH24:MI:SS')) - to_timestamp(to_char(agesch.init_time,'HH24:MI:SS'),'HH24:MI:SS')) else '00:00:00' end +
             case when agesch.friday then ((to_timestamp(to_char(agesch.final_time,'HH24:MI:SS'),'HH24:MI:SS')) - to_timestamp(to_char(agesch.init_time,'HH24:MI:SS'),'HH24:MI:SS')) else '00:00:00' end)-
            (case when agesch.monday then ((to_timestamp(to_char(agelock.final_date_time,'HH24:MI:SS'),'HH24:MI:SS')) - to_timestamp(to_char(agelock.init_date_time,'HH24:MI:SS'),'HH24:MI:SS')) else '00:00:00' end +
             case when agesch.tuesday then ((to_timestamp(to_char(agelock.final_date_time,'HH24:MI:SS'),'HH24:MI:SS')) - to_timestamp(to_char(agelock.init_date_time,'HH24:MI:SS'),'HH24:MI:SS')) else '00:00:00' end  +
             case when agesch.wednesday then ((to_timestamp(to_char(agelock.final_date_time,'HH24:MI:SS'),'HH24:MI:SS')) - to_timestamp(to_char(agelock.init_date_time,'HH24:MI:SS'),'HH24:MI:SS')) else '00:00:00' end +
             case when agesch.thursday then ((to_timestamp(to_char(agelock.final_date_time,'HH24:MI:SS'),'HH24:MI:SS')) - to_timestamp(to_char(agelock.init_date_time,'HH24:MI:SS'),'HH24:MI:SS')) else '00:00:00' end +
             case when agesch.sunday then ((to_timestamp(to_char(agelock.final_date_time,'HH24:MI:SS'),'HH24:MI:SS')) - to_timestamp(to_char(agelock.init_date_time,'HH24:MI:SS'),'HH24:MI:SS')) else '00:00:00' end +
             case when agesch.saturday then ((to_timestamp(to_char(agelock.final_date_time,'HH24:MI:SS'),'HH24:MI:SS')) - to_timestamp(to_char(agelock.init_date_time,'HH24:MI:SS'),'HH24:MI:SS')) else '00:00:00' end +
             case when agesch.friday then ((to_timestamp(to_char(agelock.final_date_time,'HH24:MI:SS'),'HH24:MI:SS')) - to_timestamp(to_char(agelock.init_date_time,'HH24:MI:SS'),'HH24:MI:SS')) else '00:00:00' end )) 
                )else
            (case when agesch.monday then ((to_timestamp(to_char(agesch.final_time,'HH24:MI:SS'),'HH24:MI:SS')) - to_timestamp(to_char(agesch.init_time,'HH24:MI:SS'),'HH24:MI:SS')) else '00:00:00' end +
             case when agesch.tuesday then ((to_timestamp(to_char(agesch.final_time,'HH24:MI:SS'),'HH24:MI:SS')) - to_timestamp(to_char(agesch.init_time,'HH24:MI:SS'),'HH24:MI:SS')) else '00:00:00' end  +
             case when agesch.wednesday then ((to_timestamp(to_char(agesch.final_time,'HH24:MI:SS'),'HH24:MI:SS')) - to_timestamp(to_char(agesch.init_time,'HH24:MI:SS'),'HH24:MI:SS')) else '00:00:00' end +
             case when agesch.thursday then ((to_timestamp(to_char(agesch.final_time,'HH24:MI:SS'),'HH24:MI:SS')) - to_timestamp(to_char(agesch.init_time,'HH24:MI:SS'),'HH24:MI:SS')) else '00:00:00' end +
             case when agesch.sunday then ((to_timestamp(to_char(agesch.final_time,'HH24:MI:SS'),'HH24:MI:SS')) - to_timestamp(to_char(agesch.init_time,'HH24:MI:SS'),'HH24:MI:SS')) else '00:00:00' end +
             case when agesch.saturday then ((to_timestamp(to_char(agesch.final_time,'HH24:MI:SS'),'HH24:MI:SS')) - to_timestamp(to_char(agesch.init_time,'HH24:MI:SS'),'HH24:MI:SS')) else '00:00:00' end +
             case when agesch.friday then ((to_timestamp(to_char(agesch.final_time,'HH24:MI:SS'),'HH24:MI:SS')) - to_timestamp(to_char(agesch.init_time,'HH24:MI:SS'),'HH24:MI:SS')) else '00:00:00' end ) end as DISPONIVEL_AGENDAMENTO
              
  from health_kernel.agenda agen
join health_kernel.agenda_schedule agesch on (agesch.agenda_key=agen.agenda_key)
left join health_kernel.agenda_schedule_lock agelock on (agelock.agenda_schedule_key=agesch.agenda_schedule_key)
where agen.deleted = false
 and agesch.deleted = false

  and agesch.init_date between '2016-04-01' and '2016-04-30'
  and agesch.final_date between '2016-04-01' and '2016-04-30'
  group by    agelock.agenda_schedule_key,
              agen.short_desc ,
              agen.agenda_key,
              agesch.agenda_schedule_key,
          agelock.final_date_time,
          agelock.init_date_time;

Link para o comentário
Compartilhar em outros sites

0 respostass a esta questão

Posts Recomendados

Até agora não há respostas para essa pergunta

Participe da discussão

Você pode postar agora e se registrar depois. Se você já tem uma conta, acesse agora para postar com sua conta.

Visitante
Responder esta pergunta...

×   Você colou conteúdo com formatação.   Remover formatação

  Apenas 75 emoticons são permitidos.

×   Seu link foi incorporado automaticamente.   Exibir como um link em vez disso

×   Seu conteúdo anterior foi restaurado.   Limpar Editor

×   Você não pode colar imagens diretamente. Carregar ou inserir imagens do URL.



  • Estatísticas dos Fóruns

    • Tópicos
      152k
    • Posts
      651,8k
×
×
  • Criar Novo...