3
\$\begingroup\$

Objective:

I want to loop through a bitarray and store subsets of that bitarray in a table.

Context:

I have a bitarray with 48 elements where each element represent one hour. I want to look back 24 hours from the start of the second day and retrieve the intervals where the last bit is 1.

I was able to achieve this but want to know if someone can provide a better solution :)

I have a table called [Numbers] that has 5000 rows that was created in accordance to this link https://www.mssqltips.com/sqlservertip/4176/the-sql-server-numbers-table-explained--part-1/ .

SCRIPT:

DECLARE @Ba NVARCHAR(48) = '000011110000000001110000000011111111000011110000'
DECLARE @numberOfIntervals INT = 24;
DECLARE @intervals TABLE( 
    SequenceId INT,
    [Periods] NVARCHAR(24)
)

INSERT INTO @intervals
SELECT number-1 AS [SequenceId], SUBSTRING(@Ba, number, @numberOfIntervals) AS [Values] 
FROM [dbo].[Numbers] 
WHERE  number > 1 AND number <= (LEN(@Ba)-(@numberOfIntervals-1)) AND RIGHT(SUBSTRING(@Ba, number, @numberOfIntervals), 1) = '1'

SELECT * FROM @intervals

RESULTS:

[SequenceId]    [Values]
_________________________
5               111000000000111000000001
6               110000000001110000000011
7               100000000011100000000111
8               000000000111000000001111
9               000000001110000000011111
10              000000011100000000111111
11              000000111000000001111111
12              000001110000000011111111
17              111000000001111111100001
18              110000000011111111000011
19              100000000111111110000111
20              000000001111111100001111
\$\endgroup\$
2
\$\begingroup\$

You're definitely on the right track by using a "Numbers" table, rather than a loop or cursor. Given the small size and the fact that you know up front that you only need 24 rows, there's no need to calculate it. You can also save a bit my creating the numbers table on the fly.

What you have now should already be reasonably fast but the in-line tally table and hard coded number of rows should beat it... If only by a small margin.

DECLARE @Ba NVARCHAR(48) = '000011110000000001110000000011111111000011110000'
DECLARE @numberOfIntervals INT = 24;
DECLARE @intervals TABLE( 
    SequenceId INT,
    [Periods] NVARCHAR(24)
);

WITH 
    cte_n (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1) ) n (n)), 
    cte_Tally (n) AS (
        SELECT TOP (24)
            ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
        FROM
            cte_n a CROSS JOIN cte_n b
        )
INSERT INTO @intervals
SELECT
    t.n - 1 AS [SequenceId],
    SUBSTRING(@Ba, t.n, @numberOfIntervals) AS [Values]
FROM
    cte_Tally t
WHERE
    SUBSTRING(@Ba, (t.n - 1)  + @numberOfIntervals, 1) = '1';

SELECT
    *
FROM
    @intervals;
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.