divide value to same number of parts using sql server

Question

Hi All, i have requirement in sql server, i need to divide(12) total (100) value as below parts. Total an divide value dynamically changes. ![75795-image.png][1] [1]: /api/attachments/75795-image.png?platform=QnA Thanks in Advance,

Answer 1

Try this:

DECLARE @dividend int = 70;
DECLARE @divisor int = 12;
SELECT CEILING(@dividend * 1.0 / @divisor);

Answer 2

(70/12 = 6)

You must have a strange calculator, with mine I get a different result of 5.83333 . Anyway, this one?

DECLARE @total int = 70, @count int = 12;   
   
;WITH cte AS   
      (SELECT 1 AS RN, @total AS RestVal, @count AS Value   
       UNION ALL   
       SELECT RN + 1, RestVal - @count, CASE WHEN RestVal - @count > @count THEN @count ELSE RestVal - @count END   
       FROM cte   
       WHERE RestVal > @count)   
SELECT RN, Value   
FROM cte   

Result

Answer 3

In addition, check a secondary possible solution, based on a relatively new approach:

declare @total int = 70
declare @d int = 11

select row_number() over (order by [value] desc) groups, [value]
from
(
    select @d
    from string_split(replicate('v', @total / @d - 1), 'v')
    union all
    select @total % @d where @total % @d <> 0
) d([value])
order by groups

Can be easily adjusted to work with more than 8000 rows.

Answer 4

Hi @BHVS ,

Welcome to Microsoft Q&A!

You could refer other experts's answer and check whether they are working.

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Please also refer below another method using loop:

drop table if exists #temp  
  
DECLARE @total int = 70, @count int = 12;   
  
DECLARE @n int=0;  
  
create table #temp   
(ID int,  
NUM int);  
  
while @n<=@total/@count  
begin  
	if @total-@count*@n>@count    
		insert into #temp values (@n+1,@count)  
	else   
		insert into #temp values (@n+1,@total-@count*@n)  
	set @n=@n+1  
end  
  
select * from #temp  

Output:

ID	NUM  
1	12  
2	12  
3	12  
4	12  
5	12  
6	10  

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.