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 [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

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

--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