Return to Question

CREATE OR ALTER PROCEDURE [dbo].[TrunkSummery]
@date datetime2(7),
-- @period int, -- TODO : set period via param = 30min, 1h etc.
AS
BEGIN
    -- Creat timeslotable
    with numbers(val) as 
        (select 1 union all select val + 1 from numbers where val < 48) -- TODO: Make 48 Parameterized
    select @date, nbr.val, 
       dateadd(MINUTE, (nbr.val - 1) * 30, @date) as period_start, 
       dateadd(MINUTE, (nbr.val    ) * 30, @date) as period_end 
    from numbers as nbr 
    order by nbr.val;

    -- Itterate
    with numbers(val)  as 
        (select 1 union all select val + 1 from numbers where val < 48), -- TODO: Make 48 Parameterized
            periods as (select @date as [date], nbr.val, 
               dateadd(SECOND, (nbr.val - 1) * @period, @date) as period_start, 
               dateadd(SECOND, (nbr.val    ) * @period, @date) as period_end 
        from numbers as nbr)
    select /* src.Trunk */, pers.period_start, src.RoutLabel, count(src.RoutLabel) as all, 
        /*count(MaxConnectionAtOnce) as max */
    --from periods as pers left  join dbo.RAW_DATA as src 
    from periods as pers inner join dbo.RAW_DATA as src 
    on src.StartDateTime >= pers.period_start and src.StartDateTime < pers.period_end
    group by /*src.Trunk*/, .period_start, pers.period_end, src.GatewayName
    order by /*src.Trunk*/, pers.period_start, pers.period_end, src.GatewayName;
END
GO

EXECUTE [dbo].[TrunkSummery] @date = '20210224', /*@period = 30*/ ;
GO

CREATE OR ALTER PROCEDURE [schemma].[TrunkSummery]
@date datetime2(7),
@period int
AS
BEGIN
    -- Creat timeslotable
        with numbers(val) as 
            (select 1 union all select val + 1 from numbers where val < 48) 
        select @date, nbr.val, 
           dateadd(minute, (nbr.val - 1) * 30, @date) as period_start, 
           dateadd(minute, (nbr.val    ) * 30, @date) as period_end 
        from numbers as nbr 
        order by nbr.val;

        with numbers(val) as 
            (select 1 union all select val + 1 from numbers where val < 48), 
        periods as (
            select @date as [date], nbr.val, 
           dateadd(minute, (nbr.val - 1) * 30, @date) as period_start, 
           dateadd(minute, (nbr.val    ) * 30, @date) as period_end 
           from numbers as nbr)
        select pers.period_start, @period as Period, src.trunk, count(src.trunk) as 'all' 
        /*, count(maxAtOnce) */ 
        --from periods as pers left  join @raw_data as src 
        from periods as pers inner join HDO.RAW_DATA as src 
        on src.StartDateTime >= pers.period_start and src.StartDateTime < pers.period_end
        group by src.trunk, pers.period_start, pers.period_end
        order by pers.period_start, src.trunk,  pers.period_end
END
GO

EXECUTE [schemma].[TrunkSummery] @date = '20210224', @period = 30;
GO

Is this a fusible idea ?

--Current output
period_start                 Period   trunk     all
2021-02-24 20:00:00.0000000  30       T1        3
2021-02-24 20:30:00.0000000  30       T1        1
2021-02-24 20:30:00.0000000  30       T2        1
2021-02-24 22:30:00.0000000  30       T1        1
2021-02-24 22:30:00.0000000  30       T2        1


-- Desired
period_start                 Period   trunk     all    max
2021-02-24 20:00:00.0000000  30       T1        3      2
2021-02-24 20:30:00.0000000  30       T1        1      1
2021-02-24 22:30:00.0000000  30       T1        1      1
2021-02-24 20:30:00.0000000  30       T2        1      1
2021-02-24 22:30:00.0000000  30       T2        1      1

Is this fusible?

Fiddle based on @SMor with new table

Fiddle based on @SMor with new table