Joining / expanding records of the same type in SQL Server

Question

I'm having problems combining records of the same type with a sequential sequence and calculating the complete sequence from the combined records.

The order must be done by the base identifier, since the sequences can tip to 0 when they reach 100. See the last entry in the I / O example.

Is it possible to have an input as shown below and have a query that produces output that is also listed below in SQL Server 2012?

Enter

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 → rollover seq

EDIT

Note that lines 6 and 7 from the source are not concatenated because they are not consecutive. Line 6 ends with 50, and line 7 begins with 75. Only consecutive lines of the same type need to be combined.

Answer 1

This problem can usually be solved with this recursion:

 create table #t ([Id] int, [Type] int, [Begin] int, [End] int); insert into #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); with cRek as ( -- records with no followup select t.[Type], FromId = t.[Id], ToId = t.[Id], t.[Begin], t.[End], [Length] = t.[End]-t.[Begin]+1 from #tt left join #t tf on tf.[Type] = t.[Type] and tf.[Begin] = (t.[End]+1)%101 and tf.[Id] > t.[Id] where tf.[Id] is null union all -- previous record select t.[Type], FromId = t.[Id], ToId = tf.[ToId], t.[Begin], tf.[End], [Length] = tf.[Length]+t.[End]-t.[Begin]+1 from #tt inner join cRek tf on tf.[Type] = t.[Type] and tf.[Begin] = (t.[End]+1)%101 and tf.[FromId] > t.[Id] ) select * from cRek r where FromId = (select min(x.FromId) from cRek x where x.[Type]=r.[Type] and x.[ToId]=r.[ToId]) order by ToId; drop table #t; 

Your sample has some minor flaws:

• 100 is followed by 0, so you have 101 elements (modulo 101!)
• Id 10 does not track Id 9 because both of them include element 10
• The length from 10 to 23 is 14, because this is the inclusion interval

HTH, Manfred

Answer 2

The last line has Begin = 10, which does not match the same rule with others. I am updating it 11 in my example. Hope this helps.

SQL Fiddler

 WITH typeRowNum AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY Id ASC) AS rownum FROM tblType ) ,rw AS ( SELECT t1.*, CASE WHEN t1.[type] = t2.[type] and ( t1.[Begin] = t2. [end] + 1 OR t1.[Begin] + 100 = t2.[end]) THEN -1 ELSE t1.rownum END AS group_id FROM typeRowNum t1 LEFT JOIN typeRowNum t2 ON t2.rownum = t1.rownum - 1 ) , cte AS ( SELECT *, new_end = ISNULL( (SELECT MIN(rownum) - 1 FROM rw r2 WHERE r2.rownum > r1.rownum and r2.group_id > r1.group_id), (SELECT MAX(rownum) FROM rw) ) FROM rw r1 WHERE r1.group_id > 0 ) select c1.id,c1.type,c1.[begin],c2.[end] ,[length] = (SELECT SUM((r.[end] - r.[Begin] + CASE WHEN r.[end] < r.[Begin] THEN 100 ELSE 0 END + CASE WHEN (r.group_id = -1) AND (r.[Begin] < r.[End]) THEN 1 ELSE 0 END) ) FROM rw r WHERE r.rownum BETWEEN c1.[rownum] AND c2.[rownum]) FROM cte c1 LEFT JOIN rw c2 ON c1.new_end = c2.rownum 

UPDATE: If you have a NULL value, most likely you have some discontinued value in the [Id] column. Instead, you can use Row_Number for JOIN. I updated my answer above.

Answer 3

hm ... a very interesting task. I got the following results:

 Type IntervalBegin CurrEnd 1 10 20 1 NULL 23 2 24 28 1 29 40 2 41 47 2 NULL 50 2 75 80 1 81 100 1 NULL 10 1 10 20 

but I'm still puzzled by the aggregation of the results ...

request below

 DECLARE @MyTable TABLE ([Id] INT, [Type] INT, [Begin] INT, [End] INT) INSERT INTO @MyTable([Id], [Type], [Begin], [End] ) 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) SELECT [Type], CASE WHEN ShouldCompareWithPrevious = 1 AND PrevBegin IS NULL THEN CurrBegin WHEN ShouldCompareWithPrevious = 1 AND PrevEnd = 100 AND CurrBegin = 0 THEN NULL WHEN ShouldCompareWithPrevious = 1 AND PrevEnd + 1 <> CurrBegin THEN CurrBegin WHEN ShouldCompareWithPrevious = 0 THEN CurrBegin ELSE NULL END IntervalBegin, CurrEnd FROM ( SELECT t1.[Id], t2.[Id] t2Id, t1.[Type], t2.[Type] t2Type, ( CASE WHEN t2.[Type] IS NULL THEN 0 WHEN t2.[Type] = t1.[Type] THEN 1 ELSE 0 END ) AS ShouldCompareWithPrevious, t1.[Begin] CurrBegin, t1.[End] CurrEnd, t2.[Begin] PrevBegin, t2.[End] PrevEnd FROM @MyTable t1 LEFT OUTER JOIN @MyTable t2 ON t1.Id = t2.Id + 1 ) intermideate