# Make geometric sequence from series of bit values

21 Reputation points
2020-11-21T11:53:39.857+00:00

I have this table:

``````declare @Table table (value int)
insert @Table select 0
insert @Table select 1
insert @Table select 1
insert @Table select 1
insert @Table select 0
insert @Table select 1
insert @Table select 1
``````

Now, I need to make a Select query, which would add a column. This column will make a geometric sequence once there is a serie of value 1 in column value.

This would be the result:

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,551 questions

1. 111.8K Reputation points
2020-11-21T13:03:28.283+00:00

I think that you need a column that specifies the order of these values, otherwise the order is undefined. The next possible approach uses an additional id column:

``````declare @Table table (id int primary key, value int)
insert @Table select 1, 0
insert @Table select 3, 1
insert @Table select 4, 1
insert @Table select 7, 1
insert @Table select 8, 0
insert @Table select 14, 1
insert @Table select 17, 1

;
with Q1 as
(
select *, isnull(lag(value) over (order by id), 0) as prev
from @Table
),
Q2 as
(
select *, (select max(id) from Q1 where id <= q.id and prev = 0) as mx
from Q1 as q
),
Q3 as
(
select *, (select count(id) from Q2 where id <= q.id and mx = q.mx) as c
from Q2 as q
)
select value, case value when 1 then POWER(2, c) else 0 end as GeometricSequence
from Q3
order by id
``````

There are alternatives, though. Maybe show your attempts too.

``````select t.*,