SQL Server 2019

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

declare @date datetime2(1);
declare @period int;
set @date = '20210224';
set @period = 30;

declare @raw_data table(GatewayName varchar(23),
    StartDateTime datetime2(1),
    DisconnectDateTime datetime2(1),
    ConnectionDuration int ,
    TrunkIngress varchar(10),
	TrunkEgress varchar(10));
insert @raw_data
    (GatewayName, StartDateTime, DisconnectDateTime, ConnectionDuration, TrunkIngress, TrunkEgress)
values
    ('GW1', '2021-02-24 20:31:51.1', '2021-02-24 20:33:23.2', DATEDIFF(millisecond, '2021-02-24 20:31:51.1', '2021-02-24 20:33:23.2'), NULL, NULL ),
    ('GW1', '2021-02-24 20:37:20.3', '2021-02-24 20:37:20.4', DATEDIFF(millisecond, '2021-02-24 20:37:20.3', '2021-02-24 20:37:20.4'), NULL, NULL ),
    ('GW1', '2021-02-24 20:37:42.7', '2021-02-24 20:37:42.8', DATEDIFF(millisecond, '2021-02-24 20:37:42.7', '2021-02-24 20:37:42.8'), NULL, NULL ),
    ('GW1', '2021-02-24 20:38:08.9', '2021-02-24 20:38:59.7', DATEDIFF(millisecond, '2021-02-24 20:38:08.9', '2021-02-24 20:38:59.7'), NULL, NULL ),
    ('GW1', '2021-02-24 20:42:35.6', '2021-02-24 20:42:38.5', DATEDIFF(millisecond, '2021-02-24 20:42:35.6', '2021-02-24 20:42:38.5'), NULL, NULL ),
    ('GW1', '2021-02-24 20:35:24.5', '2021-02-24 20:38:54.5', DATEDIFF(millisecond, '2021-02-24 20:35:24.5', '2021-02-24 20:38:54.5'), 'T2', 'E2' ),
    ('GW1', '2021-02-24 20:23:43.1', '2021-02-24 20:32:32.9', DATEDIFF(millisecond, '2021-02-24 20:23:43.1', '2021-02-24 20:32:32.9'), 'T1', 'E1' ),
    ('GW1', '2021-02-24 20:24:34.6', '2021-02-24 20:33:05.1', DATEDIFF(millisecond, '2021-02-24 20:24:34.6', '2021-02-24 20:33:05.1'), 'T1', 'E1' ),
    ('GW1', '2021-02-24 20:31:09.5', '2021-02-24 20:32:51.4', DATEDIFF(millisecond, '2021-02-24 20:31:09.5', '2021-02-24 20:32:51.4'), 'T1', 'E1' ),
    ('GW1', '2021-02-24 20:32:42.3', '2021-02-24 20:33:09.3', DATEDIFF(millisecond, '2021-02-24 20:32:42.3', '2021-02-24 20:33:09.3'), 'T1', 'E1' ),
    ('GW1', '2021-02-24 20:34:50.6', '2021-02-24 20:37:30.9', DATEDIFF(millisecond, '2021-02-24 20:34:50.6', '2021-02-24 20:37:30.9'), 'T1', 'E1' ),
    ('GW1', '2021-02-24 20:35:34.2', '2021-02-24 20:36:25.4', DATEDIFF(millisecond, '2021-02-24 20:35:34.2', '2021-02-24 20:36:25.4'), 'T1', 'E1' ),
    ('GW1', '2021-02-24 20:36:32.8', '2021-02-24 20:36:50.7', DATEDIFF(millisecond, '2021-02-24 20:36:32.8', '2021-02-24 20:36:50.7'), 'T1', 'E1' ),
    ('GW1', '2021-02-24 20:36:45.8', '2021-02-24 20:37:10.7', DATEDIFF(millisecond, '2021-02-24 20:36:45.8', '2021-02-24 20:37:10.7'), 'T1', 'E1' ),
    ('GW1', '2021-02-24 20:37:07.4', '2021-02-24 20:37:53.0', DATEDIFF(millisecond, '2021-02-24 20:37:07.4', '2021-02-24 20:37:53.0'), 'T1', 'E1' ),
    ('GW1', '2021-02-24 20:37:17.9', '2021-02-24 20:37:50.5', DATEDIFF(millisecond, '2021-02-24 20:37:17.9', '2021-02-24 20:37:50.5'), 'T1', 'E1' ),
    ('GW1', '2021-02-24 20:37:45.0', '2021-02-24 20:38:40.7', DATEDIFF(millisecond, '2021-02-24 20:37:45.0', '2021-02-24 20:38:40.7'), 'T1', 'E1' ),
    ('GW1', '2021-02-24 20:38:51.0', '2021-02-24 20:41:25.0', DATEDIFF(millisecond, '2021-02-24 20:38:51.0', '2021-02-24 20:41:25.0'), 'T1', 'E1' ),
    ('GW1', '2021-02-24 20:39:21.7', '2021-02-24 20:39:49.0', DATEDIFF(millisecond, '2021-02-24 20:39:21.7', '2021-02-24 20:39:49.0'), 'T1', 'E1' ),
    ('GW1', '2021-02-24 20:41:06.9', '2021-02-24 20:41:49.0', DATEDIFF(millisecond, '2021-02-24 20:41:06.9', '2021-02-24 20:41:49.0'), 'T1', 'E1' ),
    ('GW1', '2021-02-24 20:41:49.0', '2021-02-24 20:43:24.0', DATEDIFF(millisecond, '2021-02-24 20:41:49.0', '2021-02-24 20:43:24.0'), 'T1', 'E1' ),
    ('GW1', '2021-02-24 20:42:11.4', '2021-02-24 20:42:44.7', DATEDIFF(millisecond, '2021-02-24 20:42:11.4', '2021-02-24 20:42:44.7'), 'T1', 'E1' ),
    ('GW1', '2021-02-24 20:42:22.2', '2021-02-24 20:43:02.1', DATEDIFF(millisecond, '2021-02-24 20:42:22.2', '2021-02-24 20:43:02.1'), 'T1', 'E1' ),
    ('GW1', '2021-02-24 20:43:26.6', '2021-02-24 20:43:40.2', DATEDIFF(millisecond, '2021-02-24 20:43:26.6', '2021-02-24 20:43:40.2'), 'T1', 'E1' ),
    ('GW1', '2021-02-24 20:43:28.1', '2021-02-24 20:44:57.2', DATEDIFF(millisecond, '2021-02-24 20:43:28.1', '2021-02-24 20:44:57.2'), 'T1', 'E1' ),
    ('GW1', '2021-02-24 20:44:24.2', '2021-02-24 20:44:47.1', DATEDIFF(millisecond, '2021-02-24 20:44:24.2', '2021-02-24 20:44:47.1'), 'T1', 'E1' ),
    ('GW1', '2021-02-24 20:44:52.4', '2021-02-24 20:45:52.2', DATEDIFF(millisecond, '2021-02-24 20:44:52.4', '2021-02-24 20:45:52.2'), 'T1', 'E1' ),
    ('GW1', '2021-02-24 20:45:01.9', '2021-02-24 20:49:28.7', DATEDIFF(millisecond, '2021-02-24 20:45:01.9', '2021-02-24 20:49:28.7'), 'T1', 'E1' ),
    ('GW1', '2021-02-24 20:45:54.6', '2021-02-24 20:46:22.8', DATEDIFF(millisecond, '2021-02-24 20:45:54.6', '2021-02-24 20:46:22.8'), 'T1', 'E1' ),
    ('GW1', '2021-02-24 20:46:54.2', '2021-02-24 20:48:25.2', DATEDIFF(millisecond, '2021-02-24 20:46:54.2', '2021-02-24 20:48:25.2'), 'T1', 'E1' ),
    ('GW1', '2021-02-24 20:47:25.1', '2021-02-24 20:47:54.4', DATEDIFF(millisecond, '2021-02-24 20:47:25.1', '2021-02-24 20:47:54.4'), 'T1', 'E1' )
;
select *
from @raw_data;
-- IngressAndEgressCombined
-- Is for compability with local testing db, set to just match Ingress / Egress for now (T1 and E1 will show the same numbers)
WITH Numbers(val) AS
(
  SELECT
    1 
    
    UNION ALL
    
  SELECT
    val + 1 
  FROM
    numbers 
  WHERE
    val < 48
)
,ConnectionPeriod AS
(
  SELECT
    period.period_start
   ,period.period_end
   ,@period as Period
   ,src.Trunk
   ,CASE
      WHEN src.StartDateTime < period.period_start THEN period.period_start
      ELSE src.StartDateTime
    END AS StartDateTime
   ,CASE
      WHEN src.DisconnectDateTime > period.period_end THEN period.period_end
      ELSE src.DisconnectDateTime
    END AS DisconnectDateTime
  FROM
    (
      SELECT
        dateadd(minute, (val - 1) * 30, @date) as period_start
       ,dateadd(minute, (val    ) * 30, @date) as period_end 
      FROM
        numbers
    ) period 
  INNER JOIN
    (
      SELECT 
        TrunkIngress as Trunk
       ,StartDateTime
       ,DisconnectDateTime
      FROM 
        @raw_data

UNION ALL

SELECT 
        TrunkEgress
       ,StartDateTime
       ,DisconnectDateTime
      FROM 
        @raw_data
    ) src
      ON src.StartDateTime >= period.period_start
          AND src.StartDateTime < period.period_end
)
SELECT
  PeriodSummary.period_start
 ,PeriodSummary.Period
 ,PeriodSummary.Trunk
 ,PeriodSummary.[all]
 ,MAX(COALESCE(ConnectionSummary.ConnectionCount,0)) AS sim
FROM
  (
    SELECT
      period_start
     ,period_end
     ,@period as Period
     ,Trunk
     ,COUNT(*) as [all]
    FROM
      ConnectionPeriod
    GROUP BY
     Trunk
    ,period_start
    ,period_end
  ) PeriodSummary
INNER JOIN
  (
    SELECT
      cp1.period_start
     ,cp1.Trunk
     ,cp1.StartDateTime
     ,cp1.DisconnectDateTime
     ,COUNT(*) AS ConnectionCount
    FROM
      ConnectionPeriod cp1
    LEFT JOIN
      ConnectionPeriod cp2
        ON cp2.Trunk = cp1.Trunk
            AND cp2.StartDateTime < cp1.DisconnectDateTime
            AND cp2.DisconnectDateTime > cp1.StartDateTime
    GROUP BY
      cp1.period_start
     ,cp1.Trunk
     ,cp1.StartDateTime
     ,cp1.DisconnectDateTime
  ) ConnectionSummary
    ON ConnectionSummary.Trunk = PeriodSummary.Trunk
         AND ConnectionSummary.period_start =  PeriodSummary.Period_Start
GROUP BY
  PeriodSummary.period_start
 ,PeriodSummary.Period
 ,PeriodSummary.Trunk
 ,PeriodSummary.[all]
;

GatewayName	StartDateTime	DisconnectDateTime	ConnectionDuration	TrunkIngress	TrunkEgress
GW1	2021-02-24 20:31:51.1	2021-02-24 20:33:23.2	92100	null	null
GW1	2021-02-24 20:37:20.3	2021-02-24 20:37:20.4	100	null	null
GW1	2021-02-24 20:37:42.7	2021-02-24 20:37:42.8	100	null	null
GW1	2021-02-24 20:38:08.9	2021-02-24 20:38:59.7	50800	null	null
GW1	2021-02-24 20:42:35.6	2021-02-24 20:42:38.5	2900	null	null
GW1	2021-02-24 20:35:24.5	2021-02-24 20:38:54.5	210000	T2	E2
GW1	2021-02-24 20:23:43.1	2021-02-24 20:32:32.9	529800	T1	E1
GW1	2021-02-24 20:24:34.6	2021-02-24 20:33:05.1	510500	T1	E1
GW1	2021-02-24 20:31:09.5	2021-02-24 20:32:51.4	101900	T1	E1
GW1	2021-02-24 20:32:42.3	2021-02-24 20:33:09.3	27000	T1	E1
GW1	2021-02-24 20:34:50.6	2021-02-24 20:37:30.9	160300	T1	E1
GW1	2021-02-24 20:35:34.2	2021-02-24 20:36:25.4	51200	T1	E1
GW1	2021-02-24 20:36:32.8	2021-02-24 20:36:50.7	17900	T1	E1
GW1	2021-02-24 20:36:45.8	2021-02-24 20:37:10.7	24900	T1	E1
GW1	2021-02-24 20:37:07.4	2021-02-24 20:37:53.0	45600	T1	E1
GW1	2021-02-24 20:37:17.9	2021-02-24 20:37:50.5	32600	T1	E1
GW1	2021-02-24 20:37:45.0	2021-02-24 20:38:40.7	55700	T1	E1
GW1	2021-02-24 20:38:51.0	2021-02-24 20:41:25.0	154000	T1	E1
GW1	2021-02-24 20:39:21.7	2021-02-24 20:39:49.0	27300	T1	E1
GW1	2021-02-24 20:41:06.9	2021-02-24 20:41:49.0	42100	T1	E1
GW1	2021-02-24 20:41:49.0	2021-02-24 20:43:24.0	95000	T1	E1
GW1	2021-02-24 20:42:11.4	2021-02-24 20:42:44.7	33300	T1	E1
GW1	2021-02-24 20:42:22.2	2021-02-24 20:43:02.1	39900	T1	E1
GW1	2021-02-24 20:43:26.6	2021-02-24 20:43:40.2	13600	T1	E1
GW1	2021-02-24 20:43:28.1	2021-02-24 20:44:57.2	89100	T1	E1
GW1	2021-02-24 20:44:24.2	2021-02-24 20:44:47.1	22900	T1	E1
GW1	2021-02-24 20:44:52.4	2021-02-24 20:45:52.2	59800	T1	E1
GW1	2021-02-24 20:45:01.9	2021-02-24 20:49:28.7	266800	T1	E1
GW1	2021-02-24 20:45:54.6	2021-02-24 20:46:22.8	28200	T1	E1
GW1	2021-02-24 20:46:54.2	2021-02-24 20:48:25.2	91000	T1	E1
GW1	2021-02-24 20:47:25.1	2021-02-24 20:47:54.4	29300	T1	E1

period_start	Period	Trunk	all	sim
2021-02-24 20:00:00.0	30	E1	2	2
2021-02-24 20:00:00.0	30	T1	2	2
2021-02-24 20:30:00.0	30	E1	23	6
2021-02-24 20:30:00.0	30	E2	1	1
2021-02-24 20:30:00.0	30	T1	23	6
2021-02-24 20:30:00.0	30	T2	1	1