October 27, 2021 at 2:49 pm
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
October 27, 2021 at 3:24 pm
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/
October 27, 2021 at 3:58 pm
Hi Mike01
Brilliant that works perfectly for me. Thank you very much for that.
October 27, 2021 at 4:11 pm
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
October 28, 2021 at 1:35 pm
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