select rr.region_code,
       rab.full_name,
       rab.inn,
       rab.kpp,
       sum(case when rp.product_type_id = 1 and (rct.step_id = 5 and ((rct.initial_date <= '2019-09-30' or (rct.initial_date between '2019-07-1' and '2019-09-30' )) and ((rct.end_date between '2019-07-1' and '2019-09-30') or rct.end_date >= '2019-09-30') and ((rct.end_date - rct.initial_date) >= 10))) then 1 else 0 end) as "3кв 2019 Астрал отчет",
       sum(case when rp.product_type_id = 4 and (rct.step_id = 5 and ((rct.initial_date <= '2019-09-30' or (rct.initial_date between '2019-07-1' and '2019-09-30' )) and ((rct.end_date between '2019-07-1' and '2019-09-30') or rct.end_date >= '2019-09-30') and ((rct.end_date - rct.initial_date) >= 10))) then 1 else 0 end) as "3кв 2019 1C Отчетность",
       --sum(case when rp.product_type_id in (20,21) and (rct.step_id = 5 and ((rct.initial_date <= '2019-09-30' or (rct.initial_date between '2019-07-1' and '2019-09-30' )) and ((rct.end_date between '2019-07-1' and '2019-09-30') or rct.end_date >= '2019-09-30') and ((rct.end_date - rct.initial_date) >= 10)))then 1 else 0 end) as "3кв 2019 Астрал-ОФД + 1С-ОФД",
       sum(case when rp.product_type_id in (22,23) and (rcert.step_id = 5 and ((date(rcert.initial_time) <= '2019-09-30' or (date(rcert.initial_time) between '2019-07-1' and '2019-09-30')) and ((date(rcert.end_time) between '2019-07-1' and '2019-09-30') or date(rcert.end_time) >= '2019-09-30') and ((date(rcert.end_time) - date(rcert.initial_time)) >= 10))) then 1 else 0 end) as "3кв 2019 Астрал-ЭТ + 1С-ЭТП"
from ro_product rp
join ro_office ro on rp.office_id = ro.office_id
join ro_region rr on ro.office_id = rr.office_id
join ro_agent ra on ro.agent_id = ra.agent_id
join ro_abonent rab on ra.abonent_id = rab.abonent_id
join ro_contact rc on rp.product_id = rc.product_id
join ro_contract_tariff rct on rc.contact_id = rc.contact_id
join ro_product_person as rpp on rp.product_id = rpp.product_id
join ro_key as rk on rpp.key_id = rk.key_id
join ro_certificate as rcert on rk.certificate_id = rcert.certificate_id
where rr.region_code in ('40', '55', '71', '77')
  and rp.product_type_id in (1,4,20,21,22,23)
  and (rct.step_id = 5 and ((rct.initial_date <= '2019-09-30' or (rct.initial_date between '2019-07-1' and '2019-09-30' )) and ((rct.end_date between '2019-07-1' and '2019-09-30') or rct.end_date >= '2019-09-30') and ((rct.end_date - rct.initial_date) >= 10)))
  and (rcert.step_id = 5 and ((date(rcert.initial_time) <= '2019-09-30' or (date(rcert.initial_time) between '2019-07-1' and '2019-09-30')) and ((date(rcert.end_time) between '2019-07-1' and '2019-09-30') or date(rcert.end_time) >= '2019-09-30') and ((date(rcert.end_time) - date(rcert.initial_time)) >= 10)))
group by rr.region_code, rab.full_name, rab.inn, rab.kpp;