sql - Select first row where next 2 rows increment by + 1 and (rowVal + 2) is divisible by 3

Question

I all ready had this question partially answered by @Gordon Linoff there with was an additional requirements.

I want to find the first row where the subsequent n rows values increment by + 1.

CREATE TABLE #Temp
    ([ID] int, [cct] int)
;

INSERT INTO #Temp
    ([ID], [cct])
VALUES
    (12807, 6),
    (12813, 12),
    (12818, 17),
    (12823, 22),
    (12824, 23),
    (12830, 29),
    (12831, 30),
    (12832, 31),
    (12833, 32),
    (12835, 34),
    (12837, 36),
    (12838, 37),
    (12839, 38),
    (12840, 39),
    (12841, 40),
    (12844, 43),
    (12846, 45),
    (12847, 46),
    (12848, 47),
    (12849, 48),
    (12850, 49),
    (12851, 50),
    (12854, 53),
    (12856, 55),
    (12857, 56),
    (12860, 59),
    (12862, 61),
    (12863, 62),
    (12864, 63),
    (12865, 64),
    (12866, 65),
    (12871, 70),
    (12872, 71),
    (12873, 72)
;

@Gordon already provided me with this code to find the sequence part of it.

  select min(id),min(cct) as cct, count(*) as length
from (select s.*, (cct - row_number() over (order by id)) as grp
      from #Temp s
     ) s
group by grp
having count(*) >= 3

This works perfect to find the sequence. As can be seen from the result.

ID  cct length
12830   29  4
12837   36  5
12846   45  6
12862   61  5
12871   70  3

but I need to find the cct value within the sequence that satisfies (cct + 2) % 3 = 0

I need the query to return ID 12838,cct 37 as this is first cct value + 2 divisible by 3 that has the next 2 row values increment by 1.

Any help will be appreciated.

Answer 1

I think I have understood what you need, try this:

;with
grp as (-- get the sequences as @Gordon suggested
    select s.*, (cct - row_number() over (order by id)) as grp
    from #Temp s
),
grp_seq as (-- get the sequence position of each id
    select *, ROW_NUMBER() over (PARTITION by grp order by cct) n
    from grp
),
grp_min_max as (-- get sequence informations min/max ID, start cct and sequence length, for each group
    select grp, min(id) min_id, max(id) max_id, min(cct) as cct, count(*) as length
    from grp_seq s
    group by grp
    having count(*) >= 3
)
-- finally join all toghether to retrieve your result
select t1.ID, t1.cct, '--------->' col_sep, t1.n seq_pos, t2.ID ID_cct2, t2.cct cct_div3, t3.*
from grp_seq t1
inner join grp_seq t2 on (t1.grp=t2.grp) and (t1.cct = t2.cct-2) and (t2.cct % 3 = 0) 
inner join grp_min_max t3 on t3.grp = t1.grp
order by id

it should get all you need

ID  cct col_sep seq_pos ID_cct2 cct_div3    grp min_id  max_id  cct length
12838   37  --------->  2   12840   39  25  12837   12841   36  5
12847   46  --------->  2   12849   48  28  12846   12851   45  6
12862   61  --------->  1   12864   63  34  12862   12866   61  5
12871   70  --------->  1   12873   72  38  12871   12873   70  3

I have added some extra info (afer col_sep column) on sequence on result record expecting that ID could not be always cct + SomeValue, in this way it will give you all information available, strip off what you do not need.

I hope this helps