SELECT  nvl(r.ID_UNID_ADMIN,h.ID_UNID_ADMIN) ID_UNID_ADMIN,
        nvl(r.DT_AGEND,h.HR_INI) DT_AGEND,
        nvl(r.ATD_POS,h.ATD_POS) ATD_POS,
            nvl(r.ATD_AGD,0) ATD_AGD
    FROM( SELECT
                    ID_UNID_ADMIN,
                    HR_INI + ( level / 1440 * 10 )    as HR_INI,
                    ATD_POS
            FROM(
                SELECT  distinct
                        agen.ID_UNID_ADMIN,
                        trunc(agen.DT_AGEND) + ( VL_HORA_INI / 1440) HR_INI,
                        trunc(agen.DT_AGEND) + ( VL_HORA_FIM / 1440) HR_FIM,
                        P.QT_POSIC_ATEND ATD_POS,
                        hoag.IN_DIA_SEMANA
                  FROM ADMSPV.spv_horario_agencia hoag
                  JOIN ADMSPV.SPV_AGENDAMENTO  agen
                    ON (hoag.id_unid_admin = agen.ID_UNID_ADMIN
                   AND TO_CHAR(agen.DT_AGEND,'D')= hoag.IN_DIA_SEMANA and hoag.FL_HABILT = 'S')
                  JOIN SPV_POSICAO_AGENCIA P
                    ON (P.ID_UNID_ADMIN = hoag.ID_UNID_ADMIN
                   AND P.FL_HABILT = 'S')
                 WHERE  TRUNC(agen.DT_AGEND) = TRUNC(SYSDATE)-1 and agen.ID_UNID_ADMIN=100)
              CONNECT BY HR_INI + ( level / 1440 * 10 ) <= HR_FIM) h
   left JOIN (SELECT  A.ID_UNID_ADMIN,
                    A.DT_AGEND,
                    P.QT_POSIC_ATEND ATD_POS,
                    COUNT(*) ATD_AGD
                FROM SPV_AGENDAMENTO    A
                JOIN SPV_POSICAO_AGENCIA P
                  ON P.ID_UNID_ADMIN = A.ID_UNID_ADMIN
                 AND P.FL_HABILT = 'S'
               WHERE TRUNC(A.DT_AGEND) = TRUNC(SYSDATE)-1
                 AND P.ID_UNID_ADMIN = 100
            GROUP BY A.ID_UNID_ADMIN, A.DT_AGEND, P.QT_POSIC_ATEND) r on (H.ID_UNID_ADMIN = R.ID_UNID_ADMIN AND h.HR_INI = R.DT_AGEND)
            order by 2;