select osn.*, c1.count_1 as kol_sluchaev, c1.count_1, c2.count_2, c3.count_3,c4.count_4 from
(select distinct to_char(in_nid.appearance_date, 'DD.MM.YYYY') calc_date, in_nid.r_infectious_agent_code, in_subj.r_rf_subject_code, in_nid.infection_nidus_source_id ist_informacii, in_nid.r_infectious_agent_code vozb_infekcii, in_nid.infection_level_id ur_ochaga
from signalsm.infection_nidus in_nid
left join signalsm.infection_nidus__rf_subject in_subj
on in_nid.id=in_subj.infection_nidus_id
where parent_infection_nidus is null
group by to_char(in_nid.appearance_date, 'DD.MM.YYYY'), in_nid.r_infectious_agent_code, in_subj.r_rf_subject_code, in_nid.infection_nidus_source_id, in_nid.r_infectious_agent_code, in_nid.infection_level_id
UNION
select distinct to_char(in_nid.liquidation_date, 'DD.MM.YYYY') calc_date, in_nid.r_infectious_agent_code, in_subj.r_rf_subject_code, in_nid.infection_nidus_source_id ist_informacii, in_nid.r_infectious_agent_code vozb_infekcii, in_nid.infection_level_id ur_ochaga
from signalsm.infection_nidus in_nid
left join signalsm.infection_nidus__rf_subject in_subj
on in_nid.id=in_subj.infection_nidus_id
where parent_infection_nidus is null
group by to_char(in_nid.liquidation_date, 'DD.MM.YYYY'), in_nid.r_infectious_agent_code, in_subj.r_rf_subject_code, in_nid.infection_nidus_source_id, in_nid.r_infectious_agent_code, in_nid.infection_level_id
UNION
select distinct to_char(in_nid.confirmation_date, 'DD.MM.YYYY') calc_date, in_nid.r_infectious_agent_code, in_subj.r_rf_subject_code, in_nid.infection_nidus_source_id ist_informacii, in_nid.r_infectious_agent_code vozb_infekcii, in_nid.infection_level_id ur_ochaga
from signalsm.infection_nidus in_nid
left join signalsm.infection_nidus__rf_subject in_subj
on in_nid.id=in_subj.infection_nidus_id
where parent_infection_nidus is null
group by to_char(in_nid.confirmation_date, 'DD.MM.YYYY'), in_nid.r_infectious_agent_code, in_subj.r_rf_subject_code, in_nid.infection_nidus_source_id, in_nid.r_infectious_agent_code, in_nid.infection_level_id) osn
left join
(select to_char(in_nid.appearance_date, 'DD.MM.YYYY') app_date, in_nid.r_infectious_agent_code, in_subj.r_rf_subject_code, in_nid.infection_nidus_source_id ist_informacii, in_nid.r_infectious_agent_code vozb_infekcii, in_nid.infection_level_id ur_ochaga, count(*) count_1
from signalsm.infection_nidus in_nid
left join signalsm.infection_nidus__rf_subject in_subj
on in_nid.id=in_subj.infection_nidus_id
where parent_infection_nidus is null
group by to_char(in_nid.appearance_date, 'DD.MM.YYYY'), in_nid.r_infectious_agent_code, in_subj.r_rf_subject_code, in_nid.infection_nidus_source_id, in_nid.r_infectious_agent_code, in_nid.infection_level_id) c1
on osn.calc_date = c1.app_date and osn.r_infectious_agent_code=c1.r_infectious_agent_code and
osn.r_rf_subject_code=c1.r_rf_subject_code and osn.ist_informacii=c1.ist_informacii and osn.vozb_infekcii=c1.vozb_infekcii and osn.ur_ochaga=c1.ur_ochaga
left join
(select to_char(in_nid.liquidation_date, 'DD.MM.YYYY') liq_date, in_nid.r_infectious_agent_code, in_subj.r_rf_subject_code, in_nid.infection_nidus_source_id ist_informacii, in_nid.r_infectious_agent_code vozb_infekcii, in_nid.infection_level_id ur_ochaga, count(*) count_2
from signalsm.infection_nidus in_nid
left join signalsm.infection_nidus__rf_subject in_subj
on in_nid.id=in_subj.infection_nidus_id
where in_nid.parent_infection_nidus is null and in_nid.infection_status_id = '3'
group by to_char(in_nid.liquidation_date, 'DD.MM.YYYY'), in_nid.r_infectious_agent_code, in_subj.r_rf_subject_code, in_nid.infection_nidus_source_id, in_nid.r_infectious_agent_code, in_nid.infection_level_id) c2
on osn.calc_date=c2.liq_date and osn.r_infectious_agent_code=c2.r_infectious_agent_code and
osn.r_rf_subject_code=c2.r_rf_subject_code and osn.ist_informacii=c2.ist_informacii and osn.vozb_infekcii=c2.vozb_infekcii and osn.ur_ochaga=c2.ur_ochaga
left join
(select to_char(in_nid.liquidation_date, 'DD.MM.YYYY') liq_date, in_nid.r_infectious_agent_code, in_subj.r_rf_subject_code, in_nid.infection_nidus_source_id ist_informacii, in_nid.r_infectious_agent_code vozb_infekcii, in_nid.infection_level_id ur_ochaga, count(*) count_3
from signalsm.infection_nidus in_nid
left join signalsm.infection_nidus__rf_subject in_subj
on in_nid.id=in_subj.infection_nidus_id
where in_nid.parent_infection_nidus is null and in_nid.infection_status_id = '4'
group by to_char(in_nid.liquidation_date, 'DD.MM.YYYY'), in_nid.r_infectious_agent_code, in_subj.r_rf_subject_code, in_nid.infection_nidus_source_id, in_nid.r_infectious_agent_code, in_nid.infection_level_id) c3
on osn.calc_date=c3.liq_date and osn.r_infectious_agent_code=c3.r_infectious_agent_code and
osn.r_rf_subject_code=c3.r_rf_subject_code and osn.ist_informacii=c3.ist_informacii and osn.vozb_infekcii=c3.vozb_infekcii and osn.ur_ochaga=c3.ur_ochaga
left join
(select to_char(in_nid.confirmation_date, 'DD.MM.YYYY') con_date, in_nid.r_infectious_agent_code, in_subj.r_rf_subject_code, in_nid.infection_nidus_source_id ist_informacii, in_nid.r_infectious_agent_code vozb_infekcii, in_nid.infection_level_id ur_ochaga, count(*) count_4
from signalsm.infection_nidus in_nid
left join signalsm.infection_nidus__rf_subject in_subj
on in_nid.id=in_subj.infection_nidus_id
where in_nid.parent_infection_nidus is null
group by to_char(in_nid.confirmation_date, 'DD.MM.YYYY'), in_nid.r_infectious_agent_code, in_subj.r_rf_subject_code, in_nid.infection_nidus_source_id, in_nid.r_infectious_agent_code, in_nid.infection_level_id
) c4
on osn.calc_date=c4.con_date and osn.r_infectious_agent_code=c4.r_infectious_agent_code and
osn.r_rf_subject_code=c4.r_rf_subject_code and osn.ist_informacii=c4.ist_informacii and osn.vozb_infekcii=c4.vozb_infekcii and osn.ur_ochaga=c4.ur_ochaga