Group by bit column

All we need is an easy explanation of the problem, so here it is.

I want Group by rows with active column like result

I tray with this code and I Do not get result

    WITH CTE as (
  SELECT
    RN = ROW_NUMBER() OVER (ORDER BY id),
    *
  FROM test
)
SELECT

 convert(nvarchar(50),[Current Row].datetime,120) + ' To ' +  convert(nvarchar(50),[Next Row].datetime,120)

FROM CTE [Current Row]
LEFT JOIN CTE [Previous Row] ON
  [Previous Row].RN = [Current Row].RN - 1
 and (ISNULL( [Previous Row].active,0)=0) and [Current Row].active=1
RIGHT JOIN CTE [Next Row] ON
  [Next Row].RN = [Current Row].RN + 1 
and [Current Row].active=1 and isnull([Next Row].active,0)=0
id date active
1 2021-08-08 12:05:17.817 1
2 2021-08-08 12:05:20.817 1
3 2021-08-08 12:05:23.817 1
4 2021-08-08 12:05:26.817 1
5 2021-08-08 12:05:29.817 1
6 2021-08-08 12:05:32.817 1
7 2021-08-08 12:05:35.817 1
8 2021-08-08 12:05:38.817 1
9 2021-08-08 12:05:41.817 0
10 2021-08-08 12:05:44.817 0
11 2021-08-08 12:05:47.817 1
12 2021-08-08 12:05:50.817 1
13 2021-08-08 12:05:53.817 0
14 2021-08-08 12:05:56.817 0
15 2021-08-08 12:05:59.817 1
16 2021-08-08 12:06:02.817 0
17 2021-08-08 12:06:05.817 1
18 2021-08-08 12:06:08.817 1
19 2021-08-08 12:06:11.817 1
20 2021-08-08 12:06:14.817 0
21 2021-08-08 12:06:17.817 1
22 2021-08-08 12:06:20.817 1

and get rusult like this

date duration count
2021-08-08 12:05:17.817 To 2021-08-08 12:05:38.817 00:00:24 8
2021-08-08 12:05:47.817 To 2021-08-08 12:05:50.817 00:00:06 2
2021-08-08 12:05:56.817 To 2021-08-08 12:05:59.817 00:00:03 1
2021-08-08 12:06:05.817 To 2021-08-08 12:06:11.817 00:00:09 3
2021-08-08 12:06:17.817 To 2021-08-08 12:06:20.817 00:00:06 2

i want group by with active and date like top result

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

This is "gaps and islands" task.

WITH cte AS (
    SELECT *, 
           ROW_NUMBER() OVER (ORDER BY date) - ROW_NUMBER() OVER (PARTITION BY active ORDER BY date) [Group]
    FROM test
)
SELECT MIN(date) [From],
       MAX(date) [Till],
       COUNT(*) [Count]
FROM cte
WHERE active = 1
GROUP BY [Group]
ORDER BY [From]

fiddle (with some explanations).

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply