In SQL, convert numbers to the next available number in a range

user_15407195 0
Viewed 117

I have a table that has a list of numbers that are ages.

I want to have SQL select statement that converts the ages to the specified sequence. The user chooses the starting / ending value and the interval.

Let's say they select a sequence that starts from 3 and intervals at 2, up to 100.

The SQL code I would manually input would be:

SELECT
AGE,
IIF(AGE < 3, 3, IIF(AGE < 3+2, 3+2, IIF(AGE < 3+4, 3+4, 
  IIF(AGE < 3+6, 3+6, ..., 100)))) as AGE_ADJ
FROM
MY_TABLE

The result would be ADJ_AGE column like:

AGE ADJ_AGE
1 3
2 3
3 3
4 5
5 5
6 7
7 7
8 9

Is there a more clever way to do this? With so many ages the nested IF is too long to type out.

Thanks!

sql-server sql
2 Answers

You can construct the value ranges using values() and then use join:

select t.*, v.enda
from t left join
     (values (1, 3), (4, 5), (6, 7), (8, 9)) v(starta, enda)
     on t.age >= v.starta and t.age <= v.enda;
user_11440350

Use a CASE expression:

SELECT AGE,
       CASE 
         WHEN AGE <= :start THEN :start
         ELSE :start + :interval + (AGE - :start - 1) / :interval * :interval
       END ADJ_AGE  
FROM tablename

Replace :start and :interval with the values that you want.

See the demo.

user_104988280
Related