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