with s as(select
  q.old beg, count(*) cnt select *  table_one q
                           where q.DT between SHC_FLT_PKG.GET_BEG_DATE and SHC_FLT_PKG.GET_END_DATE
                                 and (logical_name in (select column_value from table (SHC_FLT_PKG.GET_KE())) 
                                 or (select column_value from table (SHC_FLT_PKG.GET_KE()) where rownum = 1) is null) 
                                 and (tpl_id in (select column_value from table (SHC_FLT_PKG.GET_TEMPLATE()))
                                 or (select column_value from table (SHC_FLT_PKG.GET_TEMPLATE()) where rownum = 1) is null) 
                                 and (day_of_week_number in (select column_value from table (SHC_FLT_PKG.GET_DAY_OF_WEEK()))
                                 or (select column_value from table (SHC_FLT_PKG.GET_DAY_OF_WEEK()) where rownum = 1) is null) 
                                 and (q.category in (select q.syspk name from stg_hp_sdcategorym1 q where q.type_id in (select nvl(column_value, q.type_id) from DUAL, table (SHC_FLT_PKG.GET_TYPE())(+))) or q.category is null)
                                 and (q.subcategory in (select q.syspk name from stg_hp_sdsubcategorym1 q where q.category_id in (select nvl(column_value, q.category_id) from DUAL, table (SHC_FLT_PKG.GET_CATEGORY())(+))) or q.subcategory is null)
                                 and (q.product_type in (select q.description from stg_hp_sdaream1 q where q.subcategory_id in (select nvl(column_value, q.subcategory_id) from DUAL, table (SHC_FLT_PKG.GET_SUBCATEGORY())(+))) or q.product_type is null)
                                 and (q.resolution_code in (select q.val name from stg_hp_hpcvaluesm1 q where q.state_id in (select nvl(column_value, q.state_id) from DUAL, table (SHC_FLT_PKG.GET_CLOSE_COD())(+))) or q.resolution_code is null)
                                 and (q.hpc_arrive_type in (select q.value name from stg_hp_hpccodesm1 q where q.code_id in (select nvl(column_value, q.code_id) from DUAL, table (SHC_FLT_PKG.GET_CHANNEL())(+))) or q.hpc_arrive_type is null)
                                 and (q.open_time-trunc(q.open_time)) between SHC_FLT_PKG.GET_FROM_DATE - trunc(SHC_FLT_PKG.GET_FROM_DATE) 
                                 and SHC_FLT_PKG.GET_TO_DATE - 1/86000 - trunc(SHC_FLT_PKG.GET_TO_DATE-1/86000)
                                 group by q.old),
        g as (select distinct c.old beg
                              , c.olde end
              from calendar c
              where c.day between SHC_FLT_PKG.GET_BEG_DATE and SHC_FLT_PKG.GET_END_DATE),
  abc as(select to_char(g.beg, 'dd.mm.yyyy') || '-' || to_char(g.end, 'dd.mm.yyyy') beg,
                sum(s.cnt) cnt
                from s right join g on s.beg = g.beg
                     group by g.beg, g.end order by g.beg)
  select abc.*, avg from abc left join(
                                           select sum(abc.cnt)
                                           , count(*)
                                           , sum(abc.cnt)/count(*) avg from abc)
  on 1 = 1;