code_junkie

How to group ranged values using SQL Server

Nov 13th, 2011
137
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.08 KB | None | 0 0
  1. 978412, 400
  2. 978813, 20
  3. 978834, 50
  4. 981001, 20
  5.  
  6. 978412, 472
  7. 981001, 20
  8.  
  9. SELECT
  10. MAX(t1.gapID) as gapID,
  11. t2.gapID-MAX(t1.gapID)+t2.gapSize as gapSize
  12. -- max(t1) is the specific lower bound of t2 because of the group by.
  13. FROM
  14. ( -- t1 is the lower boundary of an island.
  15. SELECT gapID
  16. FROM gaps tbl1
  17. WHERE
  18. NOT EXISTS(
  19. SELECT *
  20. FROM gaps tbl2
  21. WHERE tbl1.gapID = tbl2.gapID + tbl2.gapSize + 1
  22. )
  23. ) t1
  24. INNER JOIN ( -- t2 is the upper boundary of an island.
  25. SELECT gapID, gapSize
  26. FROM gaps tbl1
  27. WHERE
  28. NOT EXISTS(
  29. SELECT * FROM gaps tbl2
  30. WHERE tbl2.gapID = tbl1.gapID + tbl1.gapSize + 1
  31. )
  32. ) t2 ON t1.gapID <= t2.gapID -- For all t1, we get all bigger t2 and opposite.
  33. GROUP BY t2.gapID, t2.gapSize
  34.  
  35. SELECT MIN(start) AS startGroup, endGroup, (endgroup-min(start) +1) as NumNodes
  36. FROM (SELECT g1.gapID AS start,
  37. (SELECT min(g2.gapID) FROM #gaps g2
  38. WHERE g2.gapID >= g1.gapID and NOT EXISTS
  39. (SELECT * FROM #gaps g3
  40. WHERE g3.gapID - g2.gapID = 1)) as endGroup
  41. FROM #gaps g1) T1 GROUP BY endGroup
Add Comment
Please, Sign In to add comment