divide value to same number of parts using sql server

BHVS 61 Reputation points

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,

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
9,734 questions
{count} votes

4 answers

Sort by: Most helpful
  1. Guoxiong 8,076 Reputation points

    Try this:

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

  2. Olaf Helper 28,781 Reputation points

    (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   



    0 comments No comments

  3. Viorel 94,261 Reputation points

    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]
        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.

    0 comments No comments

  4. MelissaMa-MSFT 24,136 Reputation points

    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  
    	if @total-@count*@n>@count    
    		insert into #temp values (@n+1,@count)  
    		insert into #temp values (@n+1,@total-@count*@n)  
    	set @n=@n+1  
    select * from #temp  


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

    Best regards

    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.

    0 comments No comments