I have a long query that counts a few itens, based on periods. With that said, I have period P1 (which translates into 07:00:00 and 08:59:59, and so it goes. I have to calculate all the counts by medic and period . I use a binded value to search the table(trunc(a.dt_agenda, 'dd') between :dt_inicial and :dt_final ) Since I use UNION ALL to put all the periods together, the query is quite longs, and I need to store it in a varchar2 field (4000). So, I need to create an object from my query (probably a view), in a way that in the query searches the view, instead of the table. Views don't accept binded values and without the bind I can't insert the start date and end date. Anyone has any tip on how I could make this work? Thanks in advance
I've tried creating the view just including the date column in the query, but I wasn't able to make it work after (querying against the view)
SELECT
t.medico,
t.periodo,
t.capacidademaxima,
t.disponiveis,
t.usados,
t.faltas,
t.cancelamentos,
t.pa,
t.reforcopa,
t.compromissoparicular,
t.outrosempregos,
t.reunioeshdp,
t.reunioeslaboratorios,
t.almoco,
t.exameseprocedimentos,
t.hospitaisecirurgias,
t.aulas,
t.congresso,
t.ferias,
t.maternidade,
t.tratamentomedico
FROM (
SELECT
obter_desc_agenda(cd_agenda) medico,
'P1' periodo,
count(case when a.ie_status_agenda <> 'C' then 1 else null end) capacidademaxima,
count(case when nvl(a.nr_seq_motivo_transf,0) not in (30) and 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 nvl(a.nr_seq_motivo_transf,0) in (10) then 1 else null end) pa,
count(case when nvl(a.nr_seq_motivo_transf,0) in (15) then 1 else null end) reforcopa,
count(case when nvl(a.nr_seq_motivo_transf,0) in (28) then 1 else null end)compromissoparicular,
count(case when nvl(a.nr_seq_motivo_transf,0) in (12) then 1 else null end) outrosempregos,
count(case when nvl(a.nr_seq_motivo_transf,0) in (16) then 1 else null end) reunioeshdp,
count(case when nvl(a.nr_seq_motivo_transf,0) in (26) then 1 else null end) reunioeslaboratorios,
count(case when nvl(a.nr_seq_motivo_transf,0) in (20) then 1 else null end) ferias,
count(case when nvl(a.nr_seq_motivo_transf,0) in (33) then 1 else null end) almoco,
count(case when nvl(a.nr_seq_motivo_transf,0) in (27) then 1 else null end) exameseprocedimentos,
count(case when nvl(a.nr_seq_motivo_transf,0) in (11) then 1 else null end) hospitaisecirurgias,
count(case when nvl(a.nr_seq_motivo_transf,0) in (13) then 1 else null end) aulas,
count(case when nvl(a.nr_seq_motivo_transf,0) in (14) then 1 else null end) congresso,
count(case when nvl(a.nr_seq_motivo_transf,0) in (32) then 1 else null end) tratamentomedico,
count(case when nvl(a.nr_seq_motivo_transf,0) in (31) then 1 else null end) maternidade
FROM
agenda_consulta a
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')
GROUP BY
obter_desc_agenda(cd_agenda), 'P1'
UNION ALL
SELECT
obter_desc_agenda(cd_agenda) medico,
'P2' periodo,
count(case when a.ie_status_agenda <> 'C' then 1 else null end) capacidademaxima,
count(case when nvl(a.nr_seq_motivo_transf,0) not in (30) and 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 nvl(a.nr_seq_motivo_transf,0) in (10) then 1 else null end) pa,
count(case when nvl(a.nr_seq_motivo_transf,0) in (15) then 1 else null end) reforcopa,
count(case when nvl(a.nr_seq_motivo_transf,0) in (28) then 1 else null end)compromissoparicular,
count(case when nvl(a.nr_seq_motivo_transf,0) in (12) then 1 else null end) outrosempregos,
count(case when nvl(a.nr_seq_motivo_transf,0) in (16) then 1 else null end) reunioeshdp,
count(case when nvl(a.nr_seq_motivo_transf,0) in (26) then 1 else null end) reunioeslaboratorios,
count(case when nvl(a.nr_seq_motivo_transf,0) in (20) then 1 else null end) ferias,
count(case when nvl(a.nr_seq_motivo_transf,0) in (33) then 1 else null end) almoco,
count(case when nvl(a.nr_seq_motivo_transf,0) in (27) then 1 else null end) exameseprocedimentos,
count(case when nvl(a.nr_seq_motivo_transf,0) in (11) then 1 else null end) hospitaisecirurgias,
count(case when nvl(a.nr_seq_motivo_transf,0) in (13) then 1 else null end) aulas,
count(case when nvl(a.nr_seq_motivo_transf,0) in (14) then 1 else null end) congresso,
count(case when nvl(a.nr_seq_motivo_transf,0) in (32) then 1 else null end) tratamentomedico,
count(case when nvl(a.nr_seq_motivo_transf,0) in (31) then 1 else null end) maternidade
FROM
agenda_consulta a
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')
GROUP BY obter_desc_agenda(cd_agenda), 'P2'
UNION ALL
Then it repeats to P3, P4, P5 and the last part is "Total"
SELECT
obter_desc_agenda(cd_agenda) medico,
'Total' periodo,
count(case when a.ie_status_agenda <> 'C' then 1 else null end) capacidademaxima,
count(case when nvl(a.nr_seq_motivo_transf,0) not in (30) and 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 nvl(a.nr_seq_motivo_transf,0) in (10) then 1 else null end) pa,
count(case when nvl(a.nr_seq_motivo_transf,0) in (15) then 1 else null end) reforcopa,
count(case when nvl(a.nr_seq_motivo_transf,0) in (28) then 1 else null end)compromissoparicular,
count(case when nvl(a.nr_seq_motivo_transf,0) in (12) then 1 else null end) outrosempregos,
count(case when nvl(a.nr_seq_motivo_transf,0) in (16) then 1 else null end) reunioeshdp,
count(case when nvl(a.nr_seq_motivo_transf,0) in (26) then 1 else null end) reunioeslaboratorios,
count(case when nvl(a.nr_seq_motivo_transf,0) in (20) then 1 else null end) ferias,
count(case when nvl(a.nr_seq_motivo_transf,0) in (33) then 1 else null end) almoco,
count(case when nvl(a.nr_seq_motivo_transf,0) in (27) then 1 else null end) exameseprocedimentos,
count(case when nvl(a.nr_seq_motivo_transf,0) in (11) then 1 else null end) hospitaisecirurgias,
count(case when nvl(a.nr_seq_motivo_transf,0) in (13) then 1 else null end) aulas,
count(case when nvl(a.nr_seq_motivo_transf,0) in (14) then 1 else null end) congresso,
count(case when nvl(a.nr_seq_motivo_transf,0) in (32) then 1 else null end) tratamentomedico,
count(case when nvl(a.nr_seq_motivo_transf,0) in (31) then 1 else null end) maternidade
FROM
agenda_consulta a
WHERE trunc(a.dt_agenda, 'dd') between :dt_inicial and :dt_final
GROUP BY
obter_desc_agenda(cd_agenda), 'Total'
) t
order by medico, periodo
Create view from this select :
SELECT
obter_desc_agenda(cd_agenda) medico,
case
when to_char(a.dt_agenda, 'HH24:MI:SS') between ('07:00:00') and ('08:59:59') then 'P1'
when to_char(a.dt_agenda, 'HH24:MI:SS') between ('09:00:00') and ('11:59:59') then 'P2'
when to_char(a.dt_agenda, 'HH24:MI:SS') between ('12:00:00') and ('14:59:59') then 'P3'
when to_char(a.dt_agenda, 'HH24:MI:SS') between ('15:00:00') and ('16:59:59') then 'P4'
when to_char(a.dt_agenda, 'HH24:MI:SS') between ('17:00:00') and ('19:59:59') then 'P5'
end periodo,
trunc(a.dt_agenda, 'dd') date_agenda,
case when a.ie_status_agenda <> 'C' then 1 else null end capacidademaxima, -- don't count yet - only set flags (1 or null)
case when nvl(a.nr_seq_motivo_transf,0) not in (30) and a.ie_status_agenda <> 'C' then 1 else null end disponiveis,
case when a.ie_status_agenda in ('E','L','N') then 1 else null end usados,
case when a.ie_status_agenda in ('I') then 1 else null end faltas,
case when a.ie_status_agenda in ('C') then 1 else null end cancelamentos,
case when nvl(a.nr_seq_motivo_transf,0) in (10) then 1 else null end pa,
case when nvl(a.nr_seq_motivo_transf,0) in (15) then 1 else null end reforcopa,
case when nvl(a.nr_seq_motivo_transf,0) in (28) then 1 else null end compromissoparicular,
case when nvl(a.nr_seq_motivo_transf,0) in (12) then 1 else null end outrosempregos,
case when nvl(a.nr_seq_motivo_transf,0) in (16) then 1 else null end reunioeshdp,
case when nvl(a.nr_seq_motivo_transf,0) in (26) then 1 else null end reunioeslaboratorios,
case when nvl(a.nr_seq_motivo_transf,0) in (20) then 1 else null end ferias,
case when nvl(a.nr_seq_motivo_transf,0) in (33) then 1 else null end almoco,
case when nvl(a.nr_seq_motivo_transf,0) in (27) then 1 else null end exameseprocedimentos,
case when nvl(a.nr_seq_motivo_transf,0) in (11) then 1 else null end hospitaisecirurgias,
case when nvl(a.nr_seq_motivo_transf,0) in (13) then 1 else null end aulas,
case when nvl(a.nr_seq_motivo_transf,0) in (14) then 1 else null end congresso,
case when nvl(a.nr_seq_motivo_transf,0) in (32) then 1 else null end tratamentomedico,
case when nvl(a.nr_seq_motivo_transf,0) in (31) then 1 else null end maternidade
from agenda_consulta a
and use it like this:
select medico, nvl(periodo, 'Total') -- nvl is there because grouping only by medico(counting Total) gives null in periodo
, count(capacidademaxima)
, count(disponiveis)
, count(usados)
...
, count(tratamentomedico)
, count(maternidade)
from viewName
where date_agenda between :dt_inicial and :dt_final
group by grouping sets((periodo, medico), (medico)) -- it's the same as doing group by periodo, medico (counts groups P1 to P5) union all group by medico (counts Total)