select z.cz, iv.*
  from (select tst,
               to_char(tst, 'dd.mm.yyyy') d,
               to_char(tst, 'hh24:mi') || '-' ||
               to_char(tst + 1 / 48, 'hh24:mi') hm,
               sum(nvl(val, 0)) "total"
          from (select ps.tst,
                       ps.id_eq,
                       ps.n_sh,
                       round (nvl2(val_day,
                                  val_day / 48,
                                  (select val - val_day_out / 48
                                     from buf_v_int b
                                    where b.dd_mm_yyyy = ps.dd
                                      and b.n_inter_ras = ps.ni
                                      and b.n_sh =
                                          nvl(substitution_nsh, ps.n_sh)
                                      and b.n_gr_ty = 1) * port_dept * portion),
                             2) val
                  from (select dd,
                               ni,
                               s.id_eq,
                               s.n_sh,
                               substitution_nsh,
                               port_dept,
                               portion,
                               val_day,
                               val_day_out,
                               dd + (ni - 1) / 48 tst,
                               dd + ni / 48 tend
                          from (select trunc(to_date(:, 'dd.mm.yyyy hh24:mi')) +
                                       (level - 1) dd
                                  from dual
                                connect by level <= greatest(to_date(:,
                                                                     'dd.mm.yyyy hh24:mi') -
                                                             to_date(:,
                                                                     'dd.mm.yyyy hh24:mi'),
                                                             1) + 1) d,
                               (select level ni
                                  from dual
                                connect by level <= 48) d,
                               (select sp.n_sh,
                                       (select substitution_nsh
                                          from cntr4new_prg
                                         where n_sh = sp.n_sh) substitution_nsh,
                                       e.id_eq,
                                       nvl((select portion
                                             from cntr4new_prg
                                            where n_sh = sp.n_sh
                                              and id_dept =
                                              and not_use is null
                                              and lower(nvl(is_total, 'n')) != 'y'),
                                           nvl((select portion
                                                 from counters_other_dept
                                                where n_sh = sp.n_sh
                                                  and id_dept =),
                                               1)) port_dept,
                                       sp.portion,
                                       sp.val_day,
                                       nvl((select sum(val_day)
                                             from sh_eq_percent s
                                            where s.n_sh = sp.n_sh
                                              and s.id_eq not in
                                                  (select id_eq
                                                     from equipments e,
                                                          (select id_dept
                                                             from departmens
                                                           connect by prior
                                                                       id_dept =
                                                                       id_parent
                                                            start with id_dept =) d
                                                    where e.id_dept = d.id_dept)),
                                           0) val_day_out
                                  from (select t.id_eq,
                                               t.s_name,
                                               level ll,
                                               t.prn_ord
                                          from EQUIPMENTS t
                                         where t.id_dept =
                                        connect by prior t.id_eq = parent_id_eq
                                         start with parent_id_eq is null) e,
                                       sh_eq_percent sp
                                 where sp.id_eq = e.id_eq) s
                         where dd + (ni - 1) / 48 between
                               to_date(:, 'dd.mm.yyyy hh24:mi') and
                               to_date(:, 'dd.mm.yyyy hh24:mi')) ps)
         group by tst) iv,
       (select st_hour * 100 + st_min shm,
               end_hour * 100 + end_min - 1 ehm,
               substr(code_zone, 1, 1) cz
          from tarifs_ee_zone
         where id_tr = (select id_tr
                          from (select id_tr,
                                       row_number() over(order by st_date desc) rwn
                                  from tarifs_ee
                                 where st_date <=
                                       trunc(to_date(:, 'dd.mm.yyyy hh24:mi')))
                         where rwn = 1)) z
 where to_char(iv.tst, 'hh24') * 100 + to_char(iv.tst, 'mi') between z.shm and
       z.ehm
 order by iv.tst