SQL Server 2014

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

CREATE TABLE ron_UserRights_Log (
    event_id INT IDENTITY(1,1) PRIMARY KEY,
	idDenyRight INT,
	strUserID VARCHAR(100),
	strRight VARCHAR(100),
	strRole VARCHAR(100),
	strEvent VARCHAR(100),
	old_bDeleted VARCHAR(10),
	new_bDeleted VARCHAR(10),
	ts DATETIME DEFAULT GETDATE()
 );
INSERT INTO ron_UserRights_Log (idDenyRight , strUserID, strRight, strRole, strEvent, old_bDeleted,new_bDeleted,ts) 
VALUES
	(915, 24, 4, 1, 'INSERT', 0, 1, '2024-11-24 21:09:11.020'),
	(916, 24, 15, 1, 'INSERT', 0, 1, '2024-11-24 21:09:11.020'),
    (917, 24, 2, 1, 'REMOVE', NULL, 0, '2024-11-24 21:09:11.033'),
	(918, 24, 4, 1, 'REMOVE', NULL, 0, '2024-11-24 21:09:11.033'),
	(919, 24, 15, 1, 'REMOVE', NULL, 0, '2024-11-24 21:09:11.033');
5 rows affected

INSERT INTO ron_UserRights_Log (idDenyRight , strUserID, strRight, strRole, strEvent, old_bDeleted,new_bDeleted,ts) 
VALUES
	(760, 27, 12, 1, 'INSERT', 0, 1, '2024-11-24 21:11:37.140'),
	(920, 27, 9, 1, 'REMOVE', NULL, 1, '2024-11-24 21:11:37.140'),
	(921, 27, 12, 1, 'REMOVE', NULL, 0, '2024-11-24 21:11:37.140');
3 rows affected

INSERT INTO ron_UserRights_Log (idDenyRight , strUserID, strRight, strRole, strEvent, old_bDeleted,new_bDeleted,ts) 
VALUES
	(917, 24, 2, 1, 'INSERT', 0, 1, '2024-11-25 09:34:49.600'),
	(918, 24, 4, 1, 'INSERT', 0, 1, '2024-11-25 09:34:49.613'),
	(919, 24, 15, 1, 'INSERT', 0, 0, '2024-11-25 09:34:49.613'),
	(922, 24, 4, 1, 'REMOVE', NULL, 0, '2024-11-25 09:34:49.630'),
	(923, 24, 15, 1, 'REMOVE', NULL, 0, '2024-11-25 09:34:49.630');
5 rows affected

INSERT INTO ron_UserRights_Log (idDenyRight , strUserID, strRight, strRole, strEvent, old_bDeleted,new_bDeleted,ts) 
VALUES
	(924, 27, 5, 2, 'REMOVE', NULL, 0, '2024-11-26 01:11:37.140'),
	(925, 27, 8, 2, 'REMOVE', NULL, 0, '2024-11-26 01:11:37.140'),
	(926, 27, 10, 2, 'REMOVE', NULL, 0, '2024-11-26 01:11:37.140');
3 rows affected

SELECT * FROM ron_UserRights_Log ORDER BY ts, event_id


event_id
idDenyRight
strUserID
strRight
strRole
strEvent
old_bDeleted
new_bDeleted
ts


1
915
24
4
1
INSERT
0
1
2024-11-24 21:09:11.020

2
916
24
15
1
INSERT
0
1
2024-11-24 21:09:11.020

3
917
24
2
1
REMOVE
null
0
2024-11-24 21:09:11.033

4
918
24
4
1
REMOVE
null
0
2024-11-24 21:09:11.033

5
919
24
15
1
REMOVE
null
0
2024-11-24 21:09:11.033

6
760
27
12
1
INSERT
0
1
2024-11-24 21:11:37.140

7
920
27
9
1
REMOVE
null
1
2024-11-24 21:11:37.140

8
921
27
12
1
REMOVE
null
0
2024-11-24 21:11:37.140

9
917
24
2
1
INSERT
0
1
2024-11-25 09:34:49.600

10
918
24
4
1
INSERT
0
1
2024-11-25 09:34:49.613

11
919
24
15
1
INSERT
0
0
2024-11-25 09:34:49.613

12
922
24
4
1
REMOVE
null
0
2024-11-25 09:34:49.630

13
923
24
15
1
REMOVE
null
0
2024-11-25 09:34:49.630

14
924
27
5
2
REMOVE
null
0
2024-11-26 01:11:37.140

15
925
27
8
2
REMOVE
null
0
2024-11-26 01:11:37.140

16
926
27
10
2
REMOVE
null
0
2024-11-26 01:11:37.140


WITH _PreviousRowRelationChecks AS
(
  SELECT *,
    IIF(DATEDIFF(MILLISECOND, LAG(ts) OVER (ORDER BY ts, event_id), ts) <= 500, 1, 0) AS IsPreviousRowRelated    
  FROM ron_UserRights_Log
),
_Grouped AS
(
  SELECT *,
    SUM(IIF(IsPreviousRowRelated = 0, 1, 0)) OVER (ORDER BY ts, event_id ROWS UNBOUNDED PRECEDING) AS GroupId
  FROM _PreviousRowRelationChecks
),
_Sorted AS
(
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY GroupId ORDER BY ts DESC, event_id DESC) AS SortId
  FROM _Grouped
)

SELECT *
FROM _Sorted
WHERE SortId = 1;


event_id
idDenyRight
strUserID
strRight
strRole
strEvent
old_bDeleted
new_bDeleted
ts
IsPreviousRowRelated
GroupId
SortId


5
919
24
15
1
REMOVE
null
0
2024-11-24 21:09:11.033
1
1
1

8
921
27
12
1
REMOVE
null
0
2024-11-24 21:11:37.140
1
2
1

13
923
24
15
1
REMOVE
null
0
2024-11-25 09:34:49.630
1
3
1

16
926
27
10
2
REMOVE
null
0
2024-11-26 01:11:37.140
1
4
1


SELECT 
	u.event_id AS Event_ID,
	u.idDenyRight AS DenyRight_ID,
	u.strUserID AS UserName,
	u.strRight AS Permission,
	u.strRole AS Role,
	u.strEvent AS Event, 
	EventGroup.TransactionGroup AS Group_ID,
	u.ts AS TimeStamp
FROM ron_UserRights_Log u
LEFT JOIN (
SELECT
	CAST(ts AS datetime2(0)) AS EventGroupTime,
	strUserID,
	COUNT(*)  AS EventCount,
	MIN(event_id) AS FirstEventID,
	MAX(event_id) AS LastEventID,
	DENSE_RANK() OVER (ORDER BY MIN(event_id)) AS TransactionGroup
FROM
	ron_UserRights_Log
GROUP BY
	CAST(ts AS datetime2(0)), strUserID
) AS EventGroup
	ON u.strUserID = EventGroup.strUserID
	AND u.event_id >= EventGroup.FirstEventID
AND u.event_id <= EventGroup.LastEventID;


Event_ID
DenyRight_ID
UserName
Permission
Role
Event
Group_ID
TimeStamp


1
915
24
4
1
INSERT
1
2024-11-24 21:09:11.020

2
916
24
15
1
INSERT
1
2024-11-24 21:09:11.020

3
917
24
2
1
REMOVE
1
2024-11-24 21:09:11.033

4
918
24
4
1
REMOVE
1
2024-11-24 21:09:11.033

5
919
24
15
1
REMOVE
1
2024-11-24 21:09:11.033

6
760
27
12
1
INSERT
2
2024-11-24 21:11:37.140

7
920
27
9
1
REMOVE
2
2024-11-24 21:11:37.140

8
921
27
12
1
REMOVE
2
2024-11-24 21:11:37.140

9
917
24
2
1
INSERT
3
2024-11-25 09:34:49.600

10
918
24
4
1
INSERT
3
2024-11-25 09:34:49.613

11
919
24
15
1
INSERT
3
2024-11-25 09:34:49.613

12
922
24
4
1
REMOVE
3
2024-11-25 09:34:49.630

13
923
24
15
1
REMOVE
3
2024-11-25 09:34:49.630

14
924
27
5
2
REMOVE
4
2024-11-26 01:11:37.140

15
925
27
8
2
REMOVE
4
2024-11-26 01:11:37.140

16
926
27
10
2
REMOVE
4
2024-11-26 01:11:37.140


WITH UniqueValues AS (
	SELECT
		*,
		COUNT(*) OVER(PARTITION BY TABLE_A.GROUP_ID, TABLE_A.PERMISSION) AS PERMISSION_COUNT
	FROM (
		SELECT 
		u.event_id AS Event_ID,
		u.idDenyRight AS DenyRight_ID,
		u.strUserID AS UserName,
		u.strRole AS Role,
		u.strRight AS Permission,
		u.strEvent AS Event, 
		EventGroup.TransactionGroup AS Group_ID,
		u.ts AS TimeStamp
	FROM ron_UserRights_Log u
	LEFT JOIN (
	SELECT
		CAST(ts AS datetime2(0)) AS EventGroupTime,
		strUserID,
		COUNT(*)  AS EventCount,
		MIN(event_id) AS FirstEventID,
		MAX(event_id) AS LastEventID,
		DENSE_RANK() OVER (ORDER BY MIN(event_id)) AS TransactionGroup
	FROM
		ron_UserRights_Log
	GROUP BY
		CAST(ts AS datetime2(0)), strUserID
	) AS EventGroup
		ON u.strUserID = EventGroup.strUserID
		AND u.event_id >= EventGroup.FirstEventID
		AND u.event_id <= EventGroup.LastEventID
	) AS TABLE_A
)
SELECT	Group_ID,
        Event_ID,
		UserName,
		Role,
	    Permission,
		Event, 
		TimeStamp
FROM UniqueValues
WHERE PERMISSION_COUNT = 1
ORDER BY UniqueValues.TimeStamp ASC;


Group_ID
Event_ID
UserName
Role
Permission
Event
TimeStamp


1
3
24
1
2
REMOVE
2024-11-24 21:09:11.033

2
7
27
1
9
REMOVE
2024-11-24 21:11:37.140

3
9
24
1
2
INSERT
2024-11-25 09:34:49.600

4
16
27
2
10
REMOVE
2024-11-26 01:11:37.140

4
14
27
2
5
REMOVE
2024-11-26 01:11:37.140

4
15
27
2
8
REMOVE
2024-11-26 01:11:37.140

event_id	idDenyRight	strUserID	strRight	strRole	strEvent	old_bDeleted	new_bDeleted	ts
1	915	24	4	1	INSERT	0	1	2024-11-24 21:09:11.020
2	916	24	15	1	INSERT	0	1	2024-11-24 21:09:11.020
3	917	24	2	1	REMOVE	null	0	2024-11-24 21:09:11.033
4	918	24	4	1	REMOVE	null	0	2024-11-24 21:09:11.033
5	919	24	15	1	REMOVE	null	0	2024-11-24 21:09:11.033
6	760	27	12	1	INSERT	0	1	2024-11-24 21:11:37.140
7	920	27	9	1	REMOVE	null	1	2024-11-24 21:11:37.140
8	921	27	12	1	REMOVE	null	0	2024-11-24 21:11:37.140
9	917	24	2	1	INSERT	0	1	2024-11-25 09:34:49.600
10	918	24	4	1	INSERT	0	1	2024-11-25 09:34:49.613
11	919	24	15	1	INSERT	0	0	2024-11-25 09:34:49.613
12	922	24	4	1	REMOVE	null	0	2024-11-25 09:34:49.630
13	923	24	15	1	REMOVE	null	0	2024-11-25 09:34:49.630
14	924	27	5	2	REMOVE	null	0	2024-11-26 01:11:37.140
15	925	27	8	2	REMOVE	null	0	2024-11-26 01:11:37.140
16	926	27	10	2	REMOVE	null	0	2024-11-26 01:11:37.140