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

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.