I have this column of ints that represent an occurrence of a signal and I'm trying to add a column that shows the count of consecutive row
If my data looks like this
724
727
728
733
735
737
743
747
749
the resulting data with a consecutive row count column would look like this
724 1
727 1
728 2
729 3
735 1
737 1
743 1
744 2
748 1
I've done it using a looping function but I'm trying to figure out using a cte. Here is a sample of my latest attempt
DECLARE @d TABLE ( signal INT )
INSERT INTO @d
SELECT 724
UNION
SELECT 727
UNION
SELECT 728
UNION
SELECT 729
UNION
SELECT 735
UNION
SELECT 737
UNION
SELECT 743
UNION
SELECT 744
UNION
SELECT 748 ;
WITH a AS ( SELECT signal,
ROW_NUMBER() OVER ( ORDER BY signal ) AS marker
FROM @d
) ,
b AS ( SELECT a1.signal,
CASE ( a1.signal - a2.signal )
WHEN 1 THEN 1
ELSE 0
END consecutiveMarker
FROM a a1
INNER JOIN a a2 ON a2.marker = a1.marker - 1
)
SELECT *
FROM b
Produces these results
signal consecutiveMarker
727 0
728 1
729 1
735 0
737 0
743 0
744 1
748 0
The first obvious issue is missing the first signal in a series. Barring that, I thought I could then pass this to another cte with a row_number partitioning on the consecutiveMarker. That didn't work because it partitioned it as one partition. I couldn't find a way to indicate to the partitioning method that one series is separate from the next
Any help is appreciated.