Display Numbers as below style in sql server

Question

I need small help regarding sql statement:

In Sql server:

The value as 1500

Then number should be display as 2 different options like:

For Example:
Then need to return data set as
1-1000
1001-1500

That Means Here i am splitting 2nd option after 1000,so finally i need dynamically if i give any value.

Here i don't need any between numbers

One more Example:

If value=3699

Then need to return data set as
1-1000
1001-2000
2001-3000
3001-3699

Answer 1

It is not difficult to generate such intervals using recursive query:

declare @number int = 3699

;
with E as
(
    select 0 as [start], 0 as [end]
    union all
    select [end] + 1, iif(@number <= [end] + 1000, @number, [end] + 1000)
    from E
    where [end] + 1 <= @number
)
select *
from E
where [start] > 0
order by [start]
option (maxrecursion 0)

It depends on how do you want to use these numbers.

Answer 2

Hi @RAVITEJA DUDDU ,

Thank you so much for posting here.

Please refer below query and check whether it is helpful to you.

declare @num int=3699  
  
declare @n int=0  
  
declare @sql nvarchar(max)=''  
declare @statement nvarchar(max)=''  
  
WHILE ( @n <= @num/1000-1)  
BEGIN  
    SET @sql= trim(cast((@n)*1000+1 as char(10)))+'-'+trim(cast((@n+1)*1000 as char(10)))+CHAR(13)+CHAR(10)  
    SET @n  = @n  + 1  
 SET @statement=@statement+@sql  
  
END  
  
SET @statement=@statement+ trim(cast((@n)*1000+1 as char(10)))+'-'+trim(cast(@num as char(10)))  
  
print @statement  

Output:

1-1000  
1001-2000  
2001-3000  
3001-3699  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

Answer 3

Hi @RAVITEJA DUDDU ,

Thanks for your update.

It is working fine from my side when the value is 999 or 1999. Could you please check again?

Please also refer another format of output from below and check whether it is also helpful to you.

declare @num int=1999  
      
declare @n int=0  
      
declare @sql nvarchar(max)=''  
declare @statement nvarchar(max)=''  
      
WHILE ( @n <= @num/1000-1)  
BEGIN  
    SET @sql= 'select '''+trim(cast((@n)*1000+1 as char(10)))+'-'+trim(cast((@n+1)*1000 as char(10)))+''' union all '+CHAR(13)+CHAR(10)  
    SET @n  = @n  + 1  
SET @statement=@statement+@sql  
END  
      
SET @statement=@statement+ 'select '''+trim(cast((@n)*1000+1 as char(10)))+'-'+trim(cast(@num as char(10)))+''''  
      
--print @statement  
EXECUTE sp_executesql  @statement  

Output:

1-1000  
1001-1999  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

Answer 4

Hi @RAVITEJA DUDDU ,

Please find my updated query from below and hope it could be also helpful to you.

declare @num int=1000  
          
 declare @n int=0  
          
 declare @sql nvarchar(max)=''  
 declare @statement nvarchar(max)=''  
          
 WHILE ( @n <= @num/1000-1)  
 BEGIN  
     SET @sql= 'select '''+trim(cast((@n)*1000+1 as char(10)))+'-'+trim(cast((@n+1)*1000 as char(10)))+''' union all '+CHAR(13)+CHAR(10)  
     SET @n  = @n  + 1  
 SET @statement=@statement+@sql  
 END  
          
 SET @statement=@statement+ case when @num%1000<>0 then'select '''+trim(cast((@n)*1000+1 as char(10)))+'-'+trim(cast(@num as char(10)))+'''' else 'select ''''' end  
          
 --print @statement  
 EXECUTE sp_executesql  @statement  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our [documentation][5] to enable e-mail notifications if you want to receive the related email notification for this thread.