tsql - Merging/extending records with identical type in SQL Server -

i'm having problems merging records of identical types consecutive sequence , calculating full sequence merged records.

the ordering should done on basis id sequences might rollover 0 when reach 100. see last entry in input/output example.

is possible have input listed below , have query produces output listed below in sql server 2012?

input

id     type     begin     end ----------------------------- 1      1        10        20 2      1        21        23 3      2        24        28 4      1        29        40 5      2        41        47 6      2        48        50 7      2        75        80 8      1        81        100 9      1        0         10 10     1        11        20 11     1        21        5 12     1        5         6 

output

fromid     toid     type     begin     end    length ---------------------------------------------------- 1          2        1        10        23     13 (23-19) 3          3        2        24        28     4  (28-24) 4          4        1        29        40     11 (40-29) 5          6        2        41        50     9  (50-41) 7          7        2        75        80     5  (80 - 75) 8          12       1        81        20     227* 

*(100-81) + 10 + (100-11 + 20) + (100-21 + 5) + 1 -> rollovers of seq

edit

please note rows 6 , 7 source not merged because not consecutive. row 6 ends 50 , row 7 starts 75. consecutive rows same type need merged.

this problem can typically solved recursion this:

create table #t ([id] int, [type] int, [begin] int, [end] int); insert #t values (1,1,10,20),(2,1,21,23),(3,2,24,28),(4,1,29,40), (5,2,41,47),(6,2,48,50),(7,2,75,80),(8,1,81,100),(9,1,0,10),(10,1,10,20);  crek (     -- records no followup     select  t.[type], fromid = t.[id], toid = t.[id],              t.[begin], t.[end], [length] = t.[end]-t.[begin]+1        #t t     left join #t tf         on  tf.[type] = t.[type]         , tf.[begin] = (t.[end]+1)%101         , tf.[id] > t.[id]       tf.[id] null      union      -- previous record     select  t.[type], fromid = t.[id], toid = tf.[toid],              t.[begin], tf.[end], [length] = tf.[length]+t.[end]-t.[begin]+1        #t t     inner join crek tf         on  tf.[type] = t.[type]         , tf.[begin] = (t.[end]+1)%101         , tf.[fromid] > t.[id] ) select  *    crek r   fromid =          (select min(x.fromid)            crek x           x.[type]=r.[type] , x.[toid]=r.[toid]) order toid;  drop table #t; 

your sample has minor flaws:

• 100 followed 0 therefore have 101 distinct elements (modulo 101!)
• id 10 no follow-up id 9 because both include element 10
• the length 10 23 14 because including interval

hth, manfred