esouzanu

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;