Calculate number of times a number can be split evenly into pre-defined groups

  • Hi there

    I have to write a procedure that can calculate the number of times that 1 number can be divided by 2 evenly, until i get to another specifed number

    Now in the example. I have a number representing the number of records = 1,555,201

    Now I know that the denominator is 800

    So i would have the the following

    ID

    1             1,555,201

    2             777,600

    3            388,800

    4            194,400

    5            97,200

    6           48,600

    7           24,300

    8          12,150

    9          6075

    10      3037

    11      1518

    12    759

    so here i know that i can split my groups in 2 until i get to level 12

    But how am I able to precalculate that I can divide 1,555,201 by 800 X number of times , until i get to the remainder

    which is 759?

     

    I though of using of using an expression like

    WHILE (x / 800) <> 0

     

     

     

     

  • declare @NumRecords int = 1555201,
    @NumInBatch int = 800,
    @NumGroups int = 0,
    @count int = 0

    while (case when @Count = 0 then @NumInBatch else @NumRecords/(@count) end) >= @NumInBatch
    begin
    print('ID = ' + Cast(@NumGroups as varchar(10)) + ' NumRecords = ' + cast(case when @Count = 0 then @NumRecords else @NumRecords/(@count) end as varchar(10)))
    set @NumGroups = @NumGroups + 1
    set @count = case when @count = 0 then 1 else @count end * 2
    end

    print('ID = ' + Cast(@NumGroups + 1 as varchar(10)) + ' NumRecords = ' + cast(case when @Count = 0 then @NumRecords else @NumRecords/(@count) end as varchar(10)))
    Print ('Number of Groups needed is ' + Cast(@numGroups + 1 as varchar(10)))

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Hi  Mike01

    Brilliant that works perfectly for me. Thank you very much for that.

  • It's algebra which could be directly calculated, no?

    1555201/2^x=800

    2^x=1555201/800

    log10(2^x)=log10(1555201/800)

    x = log10(1555201/800) / log10(2)

    x=10.92481343

    The number of times it would need to be "applied" before it crossed the 800 barrier would be the ceiling of (x+1).  Answer: 12

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Steve Collins wrote:

    It's algebra which could be directly calculated, no?

    1555201/2^x=800

    2^x=1555201/800

    log10(2^x)=log10(1555201/800)

    x = log10(1555201/800) / log10(2)

    x=10.92481343

    The number of times it would need to be "applied" before it crossed the 800 barrier would be the ceiling of (x+1).  Answer: 12

    Great answer, Steve. Hope you don't mind if I refine it a tiny bit.

    1555201 / (2^(x-1)) < 800

    2^(x-1) > 1555201 / 800

    Take logs (base 10) and make x the subject

    x > 1 + (log(1555201/800) / log(2))

    x > 11.92

    Rounding up gives us

    x = 12

    Putting this in code

    DECLARE @Num DECIMAL(19, 6) = 1555201;
    DECLARE @Den DECIMAL(19, 6) = 800;

    SELECT Result = CEILING(1 + LOG10(@Num / @Den) / LOG10(2));

    If you aren't happy single, you won't be happy in a relationship.

    Remember, happiness comes from guitars, not relationships.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply