This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Check the below input data and expected output for grouping and counting of consecutive flags data. For example, for id (1,2) = three 0, for id (4,5) = two 1.
Input data:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
id flag ----------- ----- 1 0 2 0 3 0 4 1 5 1 6 0 7 0 8 1 9 1 10 1 11 1 12 1 |
Expected Output:
1 2 3 4 5 6 |
flag ConsecutiveCounts ----- ----------------- 0 3 0 2 1 2 1 5 |
Create a table with data:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE TABLE tbl_flags ( id INT PRIMARY KEY ,flag BIT ) GO INSERT INTO tbl_flags VALUES (1,0),(2,0),(3,0) ,(4,1),(5,1),(6,0) ,(7,0),(8,1),(9,1) ,(10,1),(11,1),(12,1) GO |
Solution:
1 2 3 4 5 6 7 8 9 10 11 12 |
;WITH CTE AS ( SELECT id ,flag ,id - ROW_NUMBER() OVER (PARTITION BY flag ORDER BY id) rnk FROM tbl_flags ) SELECT flag,(MAX(id) - MIN(id)) + 1 as ConsecutiveCounts FROM CTE GROUP BY rnk, flag ORDER BY rnk,flag |
Please try the different solution for this puzzle and share it via comment...