Loop Through bitarray to retrieve subsets of that bitarray

Question

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

Jason A. Long · Accepted Answer · 2018-11-25 11:28:53Z

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;

Stack Exchange Network

Loop Through bitarray to retrieve subsets of that bitarray

Objective:

Context:

SCRIPT:

RESULTS:

1 Answer 1

You must log in to answer this question.

Hot Network Questions

Loop Through bitarray to retrieve subsets of that bitarray

Objective:

Context:

SCRIPT:

RESULTS:

1 Answer 1

You must log in to answer this question.

Related

Hot Network Questions