Currently working on a thesis where I have been tasked with aggregating data to a new table

I'm trying to build out an aggregated table where I'll store each row group by date and a time interval (let's say 30 minutes).

I came across store procedures and think that this would be the best solution.

My source table is constructed as followed (StartDateTime = datetime2) (

StartDateTime           Name    ...   Path         
2021-02-28 20:23:43.1   Tom     ...   FG_T
2021-02-28 20:24:34.6   Tom     ...   LS_t
 ---                    ---     ...   ---
2021-02-28 20:35:09.5   Abby    ...   FS_t
2021-02-28 21:55:34.6   Tom     ...   FS_t
2021-XX-XX HH:MM:SS.s   Abby     ...  XX

My desire is to have an output table (must be a table I want to store it over time, not just temp) that counts the amount events that have happened within a given period, this procedure should be run once a day and create an entry for every available "timeslot" from the last 24h.

PeriodStart              Period(sek)    Name     Sum
2021-02-28 20:00:00.0    1800           Tom      2
2021-02-28 20:30:00.0    1800           Abby     1
2021-02-28 21:00:00.0    1800           NULL     NULL
2021-02-28 21:30:00.0    1800           Tom      1

Could someone point me in the right direction on where I could find information about creating something like this?

My thanks in advance from an aspiring SQL enthusiast :)

Update 1:

As demonstrated within @SMor's answer, I'm seeking to use some sort of tally table to generate timeslots.

Once I looked at the code he provided, I realized that I had forgotten about a few constraints that I would like to address

Clarifying my desired outcome and structure of the current tables:

This task is meant to summarize several connections coming in via a given set of ports (trunks), the newly create table (the one formed within this process) shall later be used as a hooking point for a corresponding View that will be used within an SSRS.

My goal is to count the number of incoming connections within a given timeslot per trunk (hence group by trunk as well). Once I get this first part in place, it would also be much desired to see the max amount of connections at the same time within a given slot. (Such as MaxConnectionAtOnce )

Table: RAW_DATA

I tried to add it into a store procedure, I have added block comments ( /* */ ) around the modifications I did.

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

Based on my attempts I think that I somehow need group/order on the trunks as well.

Is this a fusible idea ?

Fiddle based on @SMor with new table