Search code examples
sqlt-sqlmathclrsequences

Checking if integer exists in a variable geometric sequence

I have the following requirement (to be developed in t-sql or in a crunch - a CLR).

I need to check if a given number (X) exists in a numeric sequence where the numeric sequence is variable based on a starting number (Y) and a multiplier (Z).

Take the following example:

Y = 5
Z = 2 (known as the 'common ratio' in the math-world i think)

Sequence would be, 5, 10, 20, 40, 80 & so on

I then need to check if X exists in the given sequence.

Problem is, X, Y & Z are completely variable.

What would be the mathematical formula for testing X against sequence based on Y & Z?

I'll be writing this in T-SQL, but feel free to post the answer in any language & i'll adapt accordingly.

I'm currently reading this: http://en.wikipedia.org/wiki/Geometric_progression to try and figure it out but thought i'd ask on here incase anyone has already done it / knows the solution.

Many thanks.

Solution

  • Well, a pure SQL solution would be

    with anchor as (
  select @y as num
),
progression as (
  select num from anchor

  union all

  select num * @z from progression where num * @z <= @x
)
select case when exists (select 0 from progression where num = @x) then 1 else 0 end