Sql: Gaps And Island Problem - Date Not Consecutive Causing Rank Inaccurate

This is a follow up on the other question I asked. Quarter Segment Customer *Counter* Q1 2018 A A1 1 Q2 2018 A A1 2 Q3

Solution 1:

You need to fix your data model! In any case, this is solved by enumerating the quarters.

select quarter, customer, segment,
       row_number() over (partition by customer, segment, q_seqnum - seqnum_cs order by q_seqnum) as counter
from (select t.*,
             row_number() over (partition by customer, segment order by q_seqnum) as seqnum_cs
      from (select t.*,
                   cast(right(quarter, 4) as int) * 4 + cast(substring(quarter, 2, 1) as int) as q_seqnum
            from t
           ) t
     ) t
order by customer, q_seqnum;