1

I found this topic very helpful in solving my issue. But I would like to find solution to another problem. If number sequence looks like this 01258, then missing gaps are 3 4 7. Perfect result, however if sequence is 258, result is same and function neglects 0 and 1. Could you please help me with changes to that function so that would check gaps starting from 0? I have no idea even what to start with. Thank you.

Community
  • 1
  • 1
SinSoul
  • 83
  • 1
  • 12
  • Do you want to create user defined function to resolve tjis issue? – Saharsh Shah Jan 03 '13 at 18:31
  • Well it will be row with ids, I need sequence because everything in my site is done with JavaScript. I can't use Ajax much to check which numbers are free which are not, because Ajax is a bit slow to respond. Since everything done with dhtml I can't use submission as well. That's why I need to rearrange ids in one shot after some of lines were deleted gaps ruin my script – SinSoul Jan 03 '13 at 22:29

1 Answers1

1

Outputs a list of missing ranges as in the link provided, but within the specified range (not extensively tested).

You'll need to iterator through them to get the actual values.

CREATE TABLE tempTable AS ...

DECLARE @StartID INT ...
DECLARE @EndID INT ...

SELECT @StartID as gap_starts_at, 
       COALESCE((SELECT MIN(t3.id) -1 FROM tempTable t3
                 WHERE t3.id > @StartID AND t3.id < @EndID), @EndID) as gap_ends_at
FROM tempTable t1
WHERE NOT EXISTS (SELECT t2.id FROM tempTable t2 WHERE t2.id = @StartID)
UNION
SELECT (t1.id + 1) as gap_starts_at, 
       COALESCE((SELECT MIN(t3.id) -1 FROM tempTable t3 WHERE t3.id > t1.id),
                @EndID) as gap_ends_at
FROM #tempTable t1
WHERE NOT EXISTS (SELECT t2.id FROM tempTable t2 WHERE t2.id = t1.id + 1)
      AND id < @EndID

EDIT: Here's a link with a few ways to find missing values (I don't think any of them work with ranges though, but some may be easier to extend then others.

Bernhard Barker
  • 54,589
  • 14
  • 104
  • 138