0
I need to get the number of open calls and the number of open calls that have been answered, all grouped by month.
I’m trying like this:
select T_aberto.mes, T_aberto.qtd_aberto, T_atendido.qtd_atendido FROM
(select TO_CHAR(data_registro, 'YYYY/MM') as mes, count(incidente_id) as qtd_aberto from incidente
where incidente_id > 0
and data_registro > '01/06/2019'
group by TO_CHAR(data_registro, 'YYYY/MM')
order by TO_CHAR(data_registro, 'YYYY/MM')
) T_aberto,
(select TO_CHAR(data_registro, 'YYYY/MM') as mes, count(incidente_id) as qtd_atendido from incidente
where incidente_id > 0
and incidente.inc_status in ('c', 'p')
and data_registro > '01/06/2019'
group by TO_CHAR(data_registro, 'YYYY/MM')
order by TO_CHAR(data_registro, 'YYYY/MM')
) T_atendido
But the result is not displayed as accurate as it generates a few more lines, and out of order.
Look at:
MES QTD_ABERTO QTD_ATENDIDO
2019/06 908 902
2019/06 908 968
2019/06 908 899
2019/07 977 902
2019/07 977 968
2019/07 977 899
2019/08 965 902
2019/08 965 968
2019/08 965 899
Any suggestions how to resolve this? Thank you!
Add
where T_aberto.mes = T_atendidowould not solve?– David
Make available the structure of your tables, some sample data and the expected result of this data. So we can test solution and suggest the best
– Sorack
Dear friends, thank you for your attention. I managed to solve the issue using a CASE. select TO_CHAR(date_logged, 'YYYY/MM') date, Count(incident_id) incident_id, Count(case when inc_status in ('c', 'p') then incident_id end ) as answered from incident INNER JOIN jptsys_tabledata d on d.table_name = 'incident' Where incident_id > 0 and date_logged > '01/06/2019' group by TO_CHAR(date_logged, 'YYYY/MM') order by TO_CHAR(date_logged, 'YYYY/MM')
– Fabricio
Publish as a solution
– Motta