SQL Server 2014

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

set nocount on
declare @Programacion table (
      HoraInicio             char(5)
    , HoraFin                char(5)
    , TiempoPromedioAtencion int
    , IdProgramacion         int
)
insert @Programacion values 
    ('08:15', '12:15', 15, 79647), ('08:00', '11:00', 10, 79648)

declare @res table (IdPrg int, Orden int, HIni time(0), HFin time(0))

-- N.º máximo de Intervalos = dif en minutos / promedio
declare @i int =
    (select max(dateDiff(mi, cast(HoraInicio as time),
                             cast(HoraFin    as time)) 
                / TiempoPromedioAtencion)
        from @Programacion)
-- Bucle inverso (mayor a menor rango horario)
while @i > 0
begin
    insert @res
    select idProgramacion
        ,  @i
        ,  dateAdd(mi, (@i-1)*TiempoPromedioAtencion, cast(HoraInicio as time))
        ,  dateAdd(mi, (@i)  *TiempoPromedioAtencion, cast(HoraInicio as time))
    from @Programacion
    where  dateAdd(mi, (@i)  *TiempoPromedioAtencion, cast(HoraInicio as time))
             <= cast(HoraFin as time)
    set @i -= 1
end
select IdPrg as IdProgramacion
    , convert(char(5), HIni, 14) + ' - '
    + convert(char(5), HFin, 14) as HorasDisponible
    , HIni as HoraDisponible1
    , HFin as HoraDisponible2
from @res
order by IdPrg, Orden

IdProgramacion	HorasDisponible	HoraDisponible1	HoraDisponible2
79647	08:15 - 08:30	08:15:00	08:30:00
79647	08:30 - 08:45	08:30:00	08:45:00
79647	08:45 - 09:00	08:45:00	09:00:00
79647	09:00 - 09:15	09:00:00	09:15:00
79647	09:15 - 09:30	09:15:00	09:30:00
79647	09:30 - 09:45	09:30:00	09:45:00
79647	09:45 - 10:00	09:45:00	10:00:00
79647	10:00 - 10:15	10:00:00	10:15:00
79647	10:15 - 10:30	10:15:00	10:30:00
79647	10:30 - 10:45	10:30:00	10:45:00
79647	10:45 - 11:00	10:45:00	11:00:00
79647	11:00 - 11:15	11:00:00	11:15:00
79647	11:15 - 11:30	11:15:00	11:30:00
79647	11:30 - 11:45	11:30:00	11:45:00
79647	11:45 - 12:00	11:45:00	12:00:00
79647	12:00 - 12:15	12:00:00	12:15:00
79648	08:00 - 08:10	08:00:00	08:10:00
79648	08:10 - 08:20	08:10:00	08:20:00
79648	08:20 - 08:30	08:20:00	08:30:00
79648	08:30 - 08:40	08:30:00	08:40:00
79648	08:40 - 08:50	08:40:00	08:50:00
79648	08:50 - 09:00	08:50:00	09:00:00
79648	09:00 - 09:10	09:00:00	09:10:00
79648	09:10 - 09:20	09:10:00	09:20:00
79648	09:20 - 09:30	09:20:00	09:30:00
79648	09:30 - 09:40	09:30:00	09:40:00
79648	09:40 - 09:50	09:40:00	09:50:00
79648	09:50 - 10:00	09:50:00	10:00:00
79648	10:00 - 10:10	10:00:00	10:10:00
79648	10:10 - 10:20	10:10:00	10:20:00
79648	10:20 - 10:30	10:20:00	10:30:00
79648	10:30 - 10:40	10:30:00	10:40:00
79648	10:40 - 10:50	10:40:00	10:50:00
79648	10:50 - 11:00	10:50:00	11:00:00