By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DECLARE @data TABLE(id int, newgroup bit, idfilter tinyint, idtank tinyint, created datetime, desired_datagroup_result_example int);
INSERT INTO @data VALUES
(3541,0,2,5,'2024-07-01 08:00:01.000',102),(1740,1,1,3,'2024-07-01 08:00:01.000',100),(1739,0,1,3,'2024-07-01 08:00:02.000',100),(3540,0,2,5,'2024-07-01 08:00:02.000',102),(3539,0,2,5,'2024-07-01 08:00:03.000',102),
(1738,0,1,3,'2024-07-01 08:00:03.000',100),(1737,0,1,3,'2024-07-01 08:00:04.000',100),(3538,0,2,5,'2024-07-01 08:00:04.000',102),(3537,0,2,5,'2024-07-01 08:00:05.000',102),(5342,0,2,1,'2024-07-01 09:00:01.000',103),
(5341,0,2,1,'2024-07-01 09:00:02.000',103),(5340,0,2,1,'2024-07-01 09:00:03.000',103),(5339,0,2,1,'2024-07-01 09:00:04.000',103),(5338,0,2,1,'2024-07-01 09:00:05.000',103),(5337,0,2,1,'2024-07-01 09:00:06.000',103),
(5336,0,2,1,'2024-07-01 09:00:07.000',103),(5335,0,2,1,'2024-07-01 09:00:08.000',103),(5334,1,2,1,'2024-07-01 09:16:09.000',104),(5333,0,2,1,'2024-07-01 09:16:10.000',104),(5332,0,2,1,'2024-07-01 09:16:11.000',104),
(5331,0,2,1,'2024-07-01 09:16:12.000',104),(5344,0,2,5,'2024-07-01 10:00:01.000',105),(5343,1,1,3,'2024-07-01 10:00:01.000',101),(5345,0,1,3,'2024-07-01 10:00:02.000',101),(5346,0,2,5,'2024-07-01 10:00:02.000',105),
(5347,0,2,5,'2024-07-01 10:00:03.000',105),(5348,0,1,3,'2024-07-01 10:00:03.000',101),(5349,0,1,3,'2024-07-01 10:00:04.000',101),(5350,0,2,5,'2024-07-01 10:00:04.000',105),(5351,0,2,5,'2024-07-01 10:00:05.000',105),
(5352,0,2,1,'2024-07-01 11:00:01.000',106),(5353,0,2,1,'2024-07-01 11:00:02.000',106),(5354,0,2,1,'2024-07-01 11:00:03.000',106),(5355,0,2,1,'2024-07-01 11:00:04.000',106),(5356,0,2,1,'2024-07-01 11:00:05.000',106),
(5357,0,2,1,'2024-07-01 11:00:06.000',106),(5358,0,2,1,'2024-07-01 11:00:07.000',106),(5359,0,2,1,'2024-07-01 11:00:08.000',106),(5360,1,2,1,'2024-07-01 11:16:09.000',107),(5361,0,2,1,'2024-07-01 11:16:10.000',107),
(5362,0,2,1,'2024-07-01 11:16:11.000',107),(5363,0,2,1,'2024-07-01 11:16:12.000',107);
with cte as
(
select id, newgroup, idfilter, idtank, created,
desired = desired_datagroup_result_example,
prev_created = lag(created) over (partition by idfilter order by created)
from @data
),
cte2 as
(
SELECT *,
grp = sum( case when datediff(minute, prev_created, created) > 15
then 1
else 0
end) over (partition by idfilter order by created)
FROM cte
)
select *, datagroup = 99 + dense_rank () over (order by idfilter, grp)
from cte2
ORDER BY idfilter ASC, created ASC;
| id | newgroup | idfilter | idtank | created | desired | prev_created | grp | datagroup |
|---|---|---|---|---|---|---|---|---|
| 1740 | True | 1 | 3 | 2024-07-01 08:00:01.000 | 100 | null | 0 | 100 |
| 1739 | False | 1 | 3 | 2024-07-01 08:00:02.000 | 100 | 2024-07-01 08:00:01.000 | 0 | 100 |
| 1738 | False | 1 | 3 | 2024-07-01 08:00:03.000 | 100 | 2024-07-01 08:00:02.000 | 0 | 100 |
| 1737 | False | 1 | 3 | 2024-07-01 08:00:04.000 | 100 | 2024-07-01 08:00:03.000 | 0 | 100 |
| 5343 | True | 1 | 3 | 2024-07-01 10:00:01.000 | 101 | 2024-07-01 08:00:04.000 | 1 | 101 |
| 5345 | False | 1 | 3 | 2024-07-01 10:00:02.000 | 101 | 2024-07-01 10:00:01.000 | 1 | 101 |
| 5348 | False | 1 | 3 | 2024-07-01 10:00:03.000 | 101 | 2024-07-01 10:00:02.000 | 1 | 101 |
| 5349 | False | 1 | 3 | 2024-07-01 10:00:04.000 | 101 | 2024-07-01 10:00:03.000 | 1 | 101 |
| 3541 | False | 2 | 5 | 2024-07-01 08:00:01.000 | 102 | null | 0 | 102 |
| 3540 | False | 2 | 5 | 2024-07-01 08:00:02.000 | 102 | 2024-07-01 08:00:01.000 | 0 | 102 |
| 3539 | False | 2 | 5 | 2024-07-01 08:00:03.000 | 102 | 2024-07-01 08:00:02.000 | 0 | 102 |
| 3538 | False | 2 | 5 | 2024-07-01 08:00:04.000 | 102 | 2024-07-01 08:00:03.000 | 0 | 102 |
| 3537 | False | 2 | 5 | 2024-07-01 08:00:05.000 | 102 | 2024-07-01 08:00:04.000 | 0 | 102 |
| 5342 | False | 2 | 1 | 2024-07-01 09:00:01.000 | 103 | 2024-07-01 08:00:05.000 | 1 | 103 |
| 5341 | False | 2 | 1 | 2024-07-01 09:00:02.000 | 103 | 2024-07-01 09:00:01.000 | 1 | 103 |
| 5340 | False | 2 | 1 | 2024-07-01 09:00:03.000 | 103 | 2024-07-01 09:00:02.000 | 1 | 103 |
| 5339 | False | 2 | 1 | 2024-07-01 09:00:04.000 | 103 | 2024-07-01 09:00:03.000 | 1 | 103 |
| 5338 | False | 2 | 1 | 2024-07-01 09:00:05.000 | 103 | 2024-07-01 09:00:04.000 | 1 | 103 |
| 5337 | False | 2 | 1 | 2024-07-01 09:00:06.000 | 103 | 2024-07-01 09:00:05.000 | 1 | 103 |
| 5336 | False | 2 | 1 | 2024-07-01 09:00:07.000 | 103 | 2024-07-01 09:00:06.000 | 1 | 103 |
| 5335 | False | 2 | 1 | 2024-07-01 09:00:08.000 | 103 | 2024-07-01 09:00:07.000 | 1 | 103 |
| 5334 | True | 2 | 1 | 2024-07-01 09:16:09.000 | 104 | 2024-07-01 09:00:08.000 | 2 | 104 |
| 5333 | False | 2 | 1 | 2024-07-01 09:16:10.000 | 104 | 2024-07-01 09:16:09.000 | 2 | 104 |
| 5332 | False | 2 | 1 | 2024-07-01 09:16:11.000 | 104 | 2024-07-01 09:16:10.000 | 2 | 104 |
| 5331 | False | 2 | 1 | 2024-07-01 09:16:12.000 | 104 | 2024-07-01 09:16:11.000 | 2 | 104 |
| 5344 | False | 2 | 5 | 2024-07-01 10:00:01.000 | 105 | 2024-07-01 09:16:12.000 | 3 | 105 |
| 5346 | False | 2 | 5 | 2024-07-01 10:00:02.000 | 105 | 2024-07-01 10:00:01.000 | 3 | 105 |
| 5347 | False | 2 | 5 | 2024-07-01 10:00:03.000 | 105 | 2024-07-01 10:00:02.000 | 3 | 105 |
| 5350 | False | 2 | 5 | 2024-07-01 10:00:04.000 | 105 | 2024-07-01 10:00:03.000 | 3 | 105 |
| 5351 | False | 2 | 5 | 2024-07-01 10:00:05.000 | 105 | 2024-07-01 10:00:04.000 | 3 | 105 |
| 5352 | False | 2 | 1 | 2024-07-01 11:00:01.000 | 106 | 2024-07-01 10:00:05.000 | 4 | 106 |
| 5353 | False | 2 | 1 | 2024-07-01 11:00:02.000 | 106 | 2024-07-01 11:00:01.000 | 4 | 106 |
| 5354 | False | 2 | 1 | 2024-07-01 11:00:03.000 | 106 | 2024-07-01 11:00:02.000 | 4 | 106 |
| 5355 | False | 2 | 1 | 2024-07-01 11:00:04.000 | 106 | 2024-07-01 11:00:03.000 | 4 | 106 |
| 5356 | False | 2 | 1 | 2024-07-01 11:00:05.000 | 106 | 2024-07-01 11:00:04.000 | 4 | 106 |
| 5357 | False | 2 | 1 | 2024-07-01 11:00:06.000 | 106 | 2024-07-01 11:00:05.000 | 4 | 106 |
| 5358 | False | 2 | 1 | 2024-07-01 11:00:07.000 | 106 | 2024-07-01 11:00:06.000 | 4 | 106 |
| 5359 | False | 2 | 1 | 2024-07-01 11:00:08.000 | 106 | 2024-07-01 11:00:07.000 | 4 | 106 |
| 5360 | True | 2 | 1 | 2024-07-01 11:16:09.000 | 107 | 2024-07-01 11:00:08.000 | 5 | 107 |
| 5361 | False | 2 | 1 | 2024-07-01 11:16:10.000 | 107 | 2024-07-01 11:16:09.000 | 5 | 107 |
| 5362 | False | 2 | 1 | 2024-07-01 11:16:11.000 | 107 | 2024-07-01 11:16:10.000 | 5 | 107 |
| 5363 | False | 2 | 1 | 2024-07-01 11:16:12.000 | 107 | 2024-07-01 11:16:11.000 | 5 | 107 |
DECLARE @data TABLE(id int, newgroup bit, idfilter tinyint, idtank tinyint, created datetime, desired_datagroup_result_example int);
INSERT INTO @data VALUES
(3541,0,2,5,'2024-07-01 08:00:01.000',102),(1740,1,1,3,'2024-07-01 08:00:01.000',100),(1739,0,1,3,'2024-07-01 08:00:02.000',100),(3540,0,2,5,'2024-07-01 08:00:02.000',102),(3539,0,2,5,'2024-07-01 08:00:03.000',102),
(1738,0,1,3,'2024-07-01 08:00:03.000',100),(1737,0,1,3,'2024-07-01 08:00:04.000',100),(3538,0,2,5,'2024-07-01 08:00:04.000',102),(3537,0,2,5,'2024-07-01 08:00:05.000',102),(5342,0,2,1,'2024-07-01 09:00:01.000',103),
(5341,0,2,1,'2024-07-01 09:00:02.000',103),(5340,0,2,1,'2024-07-01 09:00:03.000',103),(5339,0,2,1,'2024-07-01 09:00:04.000',103),(5338,0,2,1,'2024-07-01 09:00:05.000',103),(5337,0,2,1,'2024-07-01 09:00:06.000',103),
(5336,0,2,1,'2024-07-01 09:00:07.000',103),(5335,0,2,1,'2024-07-01 09:00:08.000',103),(5334,1,2,1,'2024-07-01 09:16:09.000',104),(5333,0,2,1,'2024-07-01 09:16:10.000',104),(5332,0,2,1,'2024-07-01 09:16:11.000',104),
(5331,0,2,1,'2024-07-01 09:16:12.000',104),(5344,0,2,5,'2024-07-01 10:00:01.000',105),(5343,1,1,3,'2024-07-01 10:00:01.000',101),(5345,0,1,3,'2024-07-01 10:00:02.000',101),(5346,0,2,5,'2024-07-01 10:00:02.000',105),
(5347,0,2,5,'2024-07-01 10:00:03.000',105),(5348,0,1,3,'2024-07-01 10:00:03.000',101),(5349,0,1,3,'2024-07-01 10:00:04.000',101),(5350,0,2,5,'2024-07-01 10:00:04.000',105),(5351,0,2,5,'2024-07-01 10:00:05.000',105),
(5352,0,2,1,'2024-07-01 11:00:01.000',106),(5353,0,2,1,'2024-07-01 11:00:02.000',106),(5354,0,2,1,'2024-07-01 11:00:03.000',106),(5355,0,2,1,'2024-07-01 11:00:04.000',106),(5356,0,2,1,'2024-07-01 11:00:05.000',106),
(5357,0,2,1,'2024-07-01 11:00:06.000',106),(5358,0,2,1,'2024-07-01 11:00:07.000',106),(5359,0,2,1,'2024-07-01 11:00:08.000',106),(5360,1,2,1,'2024-07-01 11:16:09.000',107),(5361,0,2,1,'2024-07-01 11:16:10.000',107),
(5362,0,2,1,'2024-07-01 11:16:11.000',107),(5363,0,2,1,'2024-07-01 11:16:12.000',107);
DECLARE @data2 TABLE(id int, newgroup bit, idfilter tinyint, idtank tinyint, created datetime, desired_datagroup_result_example int, denserank_group varchar(16));
;WITH res AS(
SELECT id, newgroup, idfilter, idtank, created, desired_datagroup_result_example,
--IIF(newgroup=1,dr_newgroup,dr_main-dr_newgroup) AS denserankgroup
CONVERT(varchar(8),IIF(newgroup=1,criteriafilter_group,criteriafilter_main-criteriafilter_group))+
CONVERT(varchar(8),IIF(newgroup=1,criteriatank_group,criteriatank_main-criteriatank_group)) AS denserankgroup
FROM (
SELECT *,
-- DENSE_RANK() OVER (PARTITION BY idfilter, idtank ORDER BY idfilter, created ASC) AS dr_main, -- will generate
-- DENSE_RANK() OVER (PARTITION BY idfilter, idtank, newgroup ORDER BY idfilter, created ASC) AS dr_newgroup, -- merged islands groups
DENSE_RANK() OVER (PARTITION BY idfilter ORDER BY idfilter, created ASC) AS criteriafilter_main, -- will also
DENSE_RANK() OVER (PARTITION BY idfilter, newgroup ORDER BY idfilter, created ASC) AS criteriafilter_group, -- generate merged
DENSE_RANK() OVER (PARTITION BY idtank ORDER BY idfilter, created ASC) AS criteriatank_main, -- islands when
DENSE_RANK() OVER (PARTITION BY idtank, newgroup ORDER BY idfilter, created ASC) AS criteriatank_group -- grouping
FROM @data
) AS tmp
)INSERT INTO @data2 SELECT * FROM res;
--SELECT * FROM @data2 ORDER BY idfilter ASC, created ASC;
SELECT MIN(idfilter) AS idfilter, MIN(idtank) AS idtank, MIN(created) AS [start], MAX(created) AS [end]
FROM @data2 GROUP BY desired_datagroup_result_example ORDER BY desired_datagroup_result_example -- for comparison purpose
| idfilter | idtank | start | end |
|---|---|---|---|
| 1 | 3 | 2024-07-01 08:00:01.000 | 2024-07-01 08:00:04.000 |
| 1 | 3 | 2024-07-01 10:00:01.000 | 2024-07-01 10:00:04.000 |
| 2 | 5 | 2024-07-01 08:00:01.000 | 2024-07-01 08:00:05.000 |
| 2 | 1 | 2024-07-01 09:00:01.000 | 2024-07-01 09:00:08.000 |
| 2 | 1 | 2024-07-01 09:16:09.000 | 2024-07-01 09:16:12.000 |
| 2 | 5 | 2024-07-01 10:00:01.000 | 2024-07-01 10:00:05.000 |
| 2 | 1 | 2024-07-01 11:00:01.000 | 2024-07-01 11:00:08.000 |
| 2 | 1 | 2024-07-01 11:16:09.000 | 2024-07-01 11:16:12.000 |
| idfilter | idtank | start | end |
|---|---|---|---|
| 1 | 3 | 2024-07-01 08:00:01.000 | 2024-07-01 08:00:04.000 |
| 1 | 3 | 2024-07-01 10:00:01.000 | 2024-07-01 10:00:04.000 |
| 2 | 5 | 2024-07-01 08:00:01.000 | 2024-07-01 08:00:05.000 |
| 2 | 1 | 2024-07-01 09:00:01.000 | 2024-07-01 09:00:08.000 |
| 2 | 1 | 2024-07-01 09:16:09.000 | 2024-07-01 11:00:08.000 |
| 2 | 5 | 2024-07-01 10:00:01.000 | 2024-07-01 10:00:05.000 |
| 2 | 1 | 2024-07-01 11:16:09.000 | 2024-07-01 11:16:12.000 |