1
I have a table in oracle with start time and end of surgeries, I need to add the hours per shift (from 6 am to 11:59 am morning, 12pm to 17:59 pm is afternoon, 18h to 23:59 pm is night and 00 is dawn).
I arrived in a giant case, but the size is impractical, I also could not store the hours for two or more different shifts (ex: when the surgery starts at 10 and ends at 19h [2h for the morning shift, 6h for the afternoon period and 1 hour for the evening period). rs This is a 7:00 case only:
case when hora = 7 then
case when fim = 7 then 0 else
case when fim = 8 then 1 else
case when fim = 9 then 2 else
case when fim = 10 then 3 else
case when fim = 11 then 4 else
case when fim > 12 then 5 END END END END END END
periodo_manha,
SELECT turno, dia, mes, ano, HORAS_DISPONIVEIS, HORAS_REALIZADAS,
TRUNC(((HORAS_REALIZADAS/HORAS_DISPONIVEIS)*100))||'%' OCUPACAO
FROM(
SELECT turno, dia, mes, ano, HORAS_DISPONIVEIS,
SUM(HORAS) AS HORAS_REALIZADAS
FROM (
SELECT dia, mes, ano, turno, COUNT(*) AS HORAS,inicio, fim,
--Carga horária disponivel por dia dividido em turnos-
(CASE WHEN TURNO IN('Madrugada') OR disponibilidade in('24')
THEN '6'
ELSE
CASE WHEN TURNO IN('Manhã') AND disponibilidade in('79')
THEN '30'
ELSE
CASE WHEN TURNO IN('Manhã') AND disponibilidade in('74')
THEN '30'
ELSE
CASE WHEN TURNO IN('Tarde') AND disponibilidade in('79')
THEN '30'
ELSE
CASE WHEN TURNO IN('Tarde') AND disponibilidade in('74')
THEN '24'
ELSE
CASE WHEN TURNO IN('Noite') AND disponibilidade in('79')
THEN '13'
ELSE
CASE WHEN TURNO IN('Noite') AND disponibilidade in('74')
THEN '10'
END END END END END END END
) AS Horas_disponiveis
FROM(
-- Decode para que cada hora defina um turno--
SELECT DECODE(HORA, '6', 'Manhã', '7', 'Manhã', '8', 'Manhã', '9',
'Manhã','10','Manhã','11','Manhã',
'12', 'Tarde', '13', 'Tarde','14','Tarde',
'15','Tarde','16','Tarde','17','Tarde',
'18','Noite','19','Noite','20','Noite','21','Noite','22','Noite','23','Noite', '0','Madrugada','1','Madrugada','2','Madrugada','3','Madrugada','4','Madrugada','5','Madrugada') turno,
inicio, fim, dia, mes, ano, disponibilidade
FROM(
SELECT inicio, fim, Hora, dia, mes, ano, disponibilidade
FROM(
--Cada hora da cirurgia jogada numa coluna--
SELECT inicio, fim,
case when inicio+0 <= fim then inicio+0 else null end hora0,
case when inicio+1 <= fim then inicio+1 else null end hora1,
case when inicio+2 <= fim then inicio+2 else null end hora2,
case when inicio+3 <= fim then inicio+3 else null end hora3,
case when inicio+4 <= fim then inicio+4 else null end hora4,
case when inicio+5 <= fim then inicio+5 else null end hora5,
case when inicio+6 <= fim then inicio+6 else null end hora6,
case when inicio+7 <= fim then inicio+7 else null end hora7,
case when inicio+8 <= fim then inicio+8 else null end hora8,
case when inicio+9 <= fim then inicio+9 else null end hora9,
case when inicio+10 <= fim then inicio+10 else null end hora10,
case when inicio+11 <= fim then inicio+11 else null end hora11,
case when inicio+12 <= fim then inicio+12 else null end hora12,
case when inicio+13 <= fim then inicio+13 else null end hora13,
case when inicio+14 <= fim then inicio+14 else null end hora14,
case when inicio+15 <= fim then inicio+15 else null end hora15,
case when inicio+16 <= fim then inicio+16 else null end hora16,
case when inicio+17 <= fim then inicio+17 else null end hora17,
case when inicio+18 <= fim then inicio+18 else null end hora18,
case when inicio+19 <= fim then inicio+19 else null end hora19,
case when inicio+20 <= fim then inicio+20 else null end hora20,
case when inicio+21 <= fim then inicio+21 else null end hora21,
case when inicio+22 <= fim then inicio+22 else null end hora22,
case when inicio+23 <= fim then inicio+23 else null end hora23,
case when inicio+24 <= fim then inicio+24 else null end hora24,
dia,
mes,
ano,
disponibilidade
FROM (
SELECT to_char(a.dt_inicio_cirurgia,'hh24') inicio,
to_char(a.dt_saida_sal_cir,'hh24') fim,
to_char(a.dt_inicio_cirurgia,'dd') dia,
to_char(a.dt_inicio_cirurgia,'mm') mes,
to_char(a.dt_inicio_cirurgia,'rrrr') ano,
decode(trim(TO_CHAR(dt_realizacao,'day')),'segunda-feira','79','terça-feira','79', 'quarta-feira', '79',
'quinta-feira', '79', 'sexta-feira','79','sábado','74', 'domingo', '24') disponibilidade
--decode para pegar o dia da semana e atribuir o valor de disponibilidade em Horas por dia --
FROM cirurgia a where to_char(a.dt_inicio_cirurgia,'mm')= '05' and
to_char(a.dt_inicio_cirurgia,'rrrr')= '2018' )
)
--unpivot para transformar valores em colunas para linhas--
UNPIVOT(Hora FOR Horas IN(hora0, hora1, hora2, hora3, hora4, hora5,hora6, hora7,hora8,
hora9, hora10, hora11, hora12, hora13, hora14, hora15, hora16,
hora17, hora18, hora19, hora20, hora21, hora22, hora23, hora24)
)
)
)
group by turno, inicio, fim, dia, mes, ano, disponibilidade) group by turno, dia, mes, ano, HORAS_DISPONIVEIS) ORDER BY DIA, MES, ANO, TURNO
What is the type of the time column, or rather the structure of the intera table? Are there any columns that store start and end? You need to know how many hours are spent on shift surgery?
– David
I have the columns start, duration and end. In this case I passed, the parameter hour is the beginning of the surgery. Exactly, I need to know how many hours are spent on surgery per shift, because I still need to match the availability of staff and room to then have the percentage of use per shift.
– Ana Paula Freitas Paulino
Ana, if you have four shifts, if a surgery starts at 5:00 and goes until 7:00 in the morning, the surgeon and the team that participates in the surgery do not change, right? If the goal is to know the availability of the team, then if we only take into account the beginnings of surgery would give to know how many employees need per shift .. or you really need to know the time spent on shift surgery?
– David
It is not simple to do this consultation, but I found it very interesting, let’s wait for other people to help.
– David
Sorry David, I couldn’t edit my comment in time rs. My answer again: The surgeon and the medical team change, even in the middle of surgery. Then I need to take into account the beginning and the end to divide the hours
– Ana Paula Freitas Paulino
I did it! I made a select by turning the hours into columns, a pivot to turn into a row and then Count per turn. I wasn’t so precise because I’m not in the minutes, just the hours, but it worked
– Ana Paula Freitas Paulino
Ana, can you post your solution in an answer below? Because, who else has doubts about how to do, will have an example of how :)
– David
exceeds the maximum number of characters, as I do?
– Ana Paula Freitas Paulino
Damn, put the main part of logic
– David
Resumed: select Count(*), FROM( Select Decode(HORA, '06', 'Manhã', ...'05', 'Madrugada') turn FROM(SELECT start, end, Time FROM (select start,case when start+0 <= end then start+0 Else null end hora0, case when start+1 <= end then start+1 Else ..., end, balance from (select to_char(start,'hh24') start, to_char(exit,'hh24') end, to_char(exit,'hh24') - to_char(start,'hh24') balance from surgery)) UNPIVOT(Time FOR DAY IN(time 0,hour 1, time 2,...) ) ) )group by shift
– Ana Paula Freitas Paulino
Ana, not in a comment, put in the answer field, just below the page, in "Your answer", there fits the whole answer...
– David
Personally in this case I would create a generic function that receives by parameter the beginning and end of surgery and returns how long it took for each period in a datetime. This way would be accurate and the query would be much more readable without a lot of case. If necessary I can assemble an example as a response.
– Confundir