CODEWITHSUNDEEP
sqlsql-serversql-server-2008
ajc101
ajc101

Reputation: 49

SQL Server how to retrieve all numbers between 2 numbers

I have 4 columns- Code, Amount, Start, End. I would like to take the between amounts in the start and end columns and change them into one column with all results. Any suggestions on how to achieve this? Thanks.

Current Results:
Code  Amount  Start  End
1     5000    2015   2016
2     5000    2014   2016
3     20000   2012   2016

Desired Results:
Code  Amount  StartEnd
1     5000    2015
1     5000    2016
2     5000    2014
2     5000    2015
2     5000    2016
3     20000   2012
3     20000   2013
3     20000   2014
3     20000   2015
3     20000   2016

Upvotes: 2

Views: 5058

Answers (5)

ash
ash

Reputation: 329

If the requirements allow you to ONLY have successive numbers (like 2014, 2015, then 2016, etc) then the approach above (cte) would work fine. However, if not, you could create a another temp table (say numbers having 1 column) with numbers in successive sequence of what all you want to be the result output, like.

Number
2014
2015
2016
2018 <-- Missing 2017 and jumping on 2018
2019

And then use a right join to get results in the progressive series with a query similar to the one below.

select Code, StartEnd, Amount from numbers 
    right join inputs on number between Start and End

Upvotes: -1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can use a recursive cte to generate all the numbers between minimum start and maximum end and join on the generated numbers.

with cte as (select min(start) col,max(end) mx from tablename
             union all
             select col+1,mx from cte where col < mx)
select t.code,t.amount,c.col
from cte c
join tablename t on c.col between t.start and t.end

or more simply

with cte as (select id,amount,start startend,end from tablename
             union all
             select id,amount,start+1,end from cte where start<end)
select id,amount,startend
from cte
order by 1,3

Upvotes: 6

John Cappelletti
John Cappelletti

Reputation: 81930

Another option is a UDF. I use this TVF to generate dynamic ranges

Declare @YourTable table (Code  int, Amount int,  Start int , [End] int)
Insert into @YourTable values 
(1,5000 ,2015,2016),
(2,5000 ,2014,2016),
(3,20000,2012,2016)

Select A.Code
      ,A.Amount
      ,StartEnd = cast(B.RetVal as int)
 From @YourTable A
 Cross Apply (Select * from [dbo].[udf-Range-Number](A.Start,A.[End],1)) B

Returns

Code    Amount  StartEnd
1       5000    2015
1       5000    2016
2       5000    2014
2       5000    2015
2       5000    2016
3       20000   2012
3       20000   2013
3       20000   2014
3       20000   2015
3       20000   2016

The Function

CREATE FUNCTION [dbo].[udf-Range-Number] (@R1 money,@R2 money,@Incr money)
Returns Table
Return (
    with cte0(M) As (Select cast((@R2-@R1)/@Incr as int)),
         cte1(N) As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
         cte2(N) As (Select Top (Select M from cte0) Row_Number() over (Order By (Select NULL)) From cte1 a,cte1 b,cte1 c,cte1 d,cte1 e,cte1 f,cte1 g,cte1 h )

    Select RetSeq=1,RetVal=@R1 Union All Select N+1,(N*@Incr)+@R1
    From   cte2   
)
/*
Max 100 million observations -- 
Syntax:
Select * from [dbo].[udf-Range-Number](0,4,0.25)
*/

Upvotes: 1

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

You can query like this

SELECT
    c.code,
    c.amount,
    f.yr
FROM #code c
CROSS APPLY fn_yearslist(c.startyr, c.endyr) f

function you cancreate like this

CREATE FUNCTION fn_yearslist (@startyear int, @endyear int)
RETURNS @t TABLE (
    yr int
)
AS
BEGIN
    WHILE (@startyear <= @endyear)
    BEGIN
    INSERT INTO @t (yr)
        VALUES (@startyear)
    SET @startyear += 1
    END
    RETURN
END

Upvotes: 1

G&#225;bor Lőrincz
G&#225;bor Lőrincz

Reputation: 51

I am not sure if it works on SQL 2008, but here's a CTE:

;with sel_cte as (
select Code, Amount, start StartEnd
from @tblTest t

union all

select t.code, t.amount, c.StartEnd + 1 StartEnd
from sel_cte c
  inner join @tblTest t on t.code = c.code
where c.StartEnd + 1 <= [end]
)
select *
from sel_cte
order by Code, StartEnd

Note: replace @tblTest with the actual table name.

Upvotes: 0

Related Questions