How to find ladder in sql query

• karunakar2351

  Right there with Babe

  Points: 729

  More actions

  July 22, 2013 at 11:58 pm

  #276226

  Hi,

  I have table like below

  col1

  5

  10

  15

  20

  I need result like

  Col1 Col2

  5 05--9

  10 10--14

  15 15--19

  20 20-24

  Plz help

  Thank You

• Nakul Vachhrajani

  SSChampion

  Points: 10294

  More actions

  July 23, 2013 at 12:20 am

  #1634762

  Assuming that the first value in the table gives me the ladder increment, does this work for you?

  USE tempdb ;

  GO

  DECLARE @ladderTable TABLE ( Col1 INT ) ;

  DECLARE @incrementValue INT = 0 ;

  INSERT INTO @ladderTable ( Col1 )

  VALUES ( 5 ),

  ( 10 ),

  ( 15 ),

  ( 20 ) ;

  SELECT TOP 1

  @incrementValue = Col1

  FROM @ladderTable

  ORDER BY Col1 ASC ;

  SELECT st.Col1,

  CAST(st.Col1 AS VARCHAR(20)) + ' - '

  + CAST(ISNULL(dt.Col1, st.Col1 + @incrementValue) - 1 AS VARCHAR(20)) AS LadderSequence

  FROM @ladderTable AS st

  LEFT OUTER JOIN @ladderTable AS dt ON st.Col1 < dt.Col1

  AND ( st.Col1 + @incrementValue ) >= dt.Col1 ;

  GO

  Thanks & Regards,
  Nakul Vachhrajani.
  http://nakulvachhrajani.com

  Follow me on
  Twitter: @sqltwins

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  July 23, 2013 at 12:38 am

  #1634768

  Hi Nakul,

  NIce solution. But one thing. That solution works only for the input data provided by the OP. Try your query on this sample set and u'll notice the result set is bit off.

  INSERT INTO @ladderTable ( Col1 )

  VALUES ( 5 ),

  ( 12 ),

  ( 19 ),

  ( 20 ) ;

  I think this is a classic case of recursion. We could either use recursive CTE or Jeff's Running Totals solutions (http://www.sqlservercentral.com/articles/T-SQL/68467/).

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  July 23, 2013 at 2:25 am

  #1634803

  drop TABLE #Temp

  CREATE TABLE #Temp (col1 INT)

  INSERT INTO #Temp (col1) VALUES (5),(10),(15),(20)

  ;WITH SequencedData AS (SELECT col1, seq = ROW_NUMBER() OVER(ORDER BY col1) FROM #Temp)

  SELECT

  tr.col1,

  col2 = ISNULL(nr.col1-1,24)

  FROM SequencedData tr

  LEFT JOIN SequencedData nr ON nr.seq = tr.seq+1

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden