# Retrieve sum of specific bitarray sequences

I have a table that has the following data:

SequenceID Values
____________________________________
1   111111111111111111111100000111110000
2   111111111111000000000000011110000001
3   111111111110000000000000000000000011
4   111111111100000111111000000000000111
5   111111111000000011111111100000001111
6   111111110000000000111111110000011111
7   111111100000000111111111000000111111
8   111111000000001111111000000000111111
9   111110000000011111100000000000111111
10  111100000000000000000000000000111111

What would be the best way to retrieve the sum of the sequences that only have zeros according to a specified parameter. For example if I wanted to retrieve the sum of the 2 largest sub-sequences of zeros I would get:

SequenceID  Zeros
_________________
1   9
2   19
3   23
4   17
5   14
6   15
7   14
8   17
9   19
10  26

If the specified parameter was 1 I would get:

SequenceID  Zeros
_________________
1   5
2   13
3   23
4   12
5   7
6   10
7   8
8   9
9   11
10  26

I have managed to achieve this, but on a azure database if I call this multiple times (62 times to be more precise) it takes too long. Locally it takes 3 secs and on Azure 26 secs. So I believe something in my script can be improved.

### Solution I came up with

DECLARE @Hours NVARCHAR(1) = 0
DECLARE @Splitter VARCHAR(1) = CASE WHEN @Hours = '0' THEN '1' ELSE '0' END
DECLARE @NumberOfPeriodsToCalc INT = 2

DECLARE @table TABLE (SequenceID INT, [Values] CHAR(36));
INSERT @table VALUES
(1 ,  111111111111111111111100000111110000),
(2 ,  111111111111000000000000011110000001),
(3 ,  111111111110000000000000000000000011),
(4 ,  111111111100000111111000000000000111),
(5 ,  111111111000000011111111100000001111),
(6 ,  111111110000000000111111110000011111),
(7 ,  111111100000000111111111000000111111),
(8 ,  111111000000001111111000000000111111),
(9 ,  111110000000011111100000000000111111),
(10,  111100000000000000000000000000111111);

;WITH PeriodHours ([SequenceId],[Values]) AS
(
SELECT SequenceId, (LEN(value))
FROM @table t
CROSS APPLY(SELECT replace(replace(replace(t.[Values],@Splitter,'<>'),'><',''),'<>',@Splitter)) s(r)
CROSS APPLY STRING_SPLIT(s.r, @Splitter)
WHERE value != ''
),
SummedPeriodHours  AS
(
SELECT   *,
ROW_NUMBER() OVER (PARTITION BY [SequenceId] ORDER BY [Values] DESC) AS rn
FROM PeriodHours
),
Results ([SequenceId],[Values]) AS (
SELECT SequenceId, sum([Values])
FROM SummedPeriodHours
WHERE rn <= @NumberOfPeriodsToCalc
GROUP BY SequenceId
)

SELECT * from Results