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

Question

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

Weegee71

SSChasing Mays

Points: 604
More actions
October 27, 2021 at 2:49 pm

#3945087

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

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

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

Mike01 SSChampion Points: 11395 More actions · Answer 1

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/

Weegee71 SSChasing Mays Points: 604 More actions · Answer 2

Hi Mike01

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

Steve Collins SSCrazy Points: 2157 More actions · Answer 3

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

This reply was modified 3 years ago by Steve Collins.

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 4

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));

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin