Answered by:
Running Total Percentage
Question

Hi All,
I have a requirement to calculate the table with rows like below and wanted to calculate the percentage of the data
EntriesRemaining NoOfEntries 100 175 0.856516977 149.890471 0.699488491 104.8466594 0.71297989 74.75355971 0.944871795 70.63253014 0.900949797 63.63636365 So from above data 100% is 175 and next row I want is 85% of 175 which brings 149.89 this result is used to calculate the percentage of next row which is 69% of 149.89 which is 104.84.. and so on.
Thanks in Advance.
Monday, February 22, 2016 8:20 PM
Answers

Using Stefan's sample table, here is the solution:
DECLARE @Sample TABLE ( OrderNo INT, EntriesRemaining FLOAT, NoOfEntries FLOAT, PRIMARY KEY ( OrderNo ) );
INSERT INTO @Sample
VALUES ( 1, 100, 175 ),
( 2, 0.856516977, 149.890471 ),
( 3, 0.699488491, 104.8466594 ),
( 4, 0.71297989, 74.75355971 ),
( 5, 0.944871795, 70.63253014 ), ( 6, 0.900949797, 63.63636365 );SELECT *, 175 * power(1E1, SUM(LOG10(EntriesRemaining)) OVER (ORDER BY OrderNo ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))
FROM @Sample
WHERE OrderNo > 1The problem is a running total. There is no product aggregator in SQL Server. However, as long as all values in the product are positive, we can use the fact that a*b = n^(logn(a) + logn(b)), which is what I use here. (With n = 10.)
The solution requires SQL 2012 so that we can use the window aggregate.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
 Proposed as answer by Naomi N Monday, February 22, 2016 11:19 PM
 Marked as answer by Sam ZhaMicrosoft contingent staff Friday, March 4, 2016 8:54 AM
Monday, February 22, 2016 11:15 PM
All replies

First of all: This is absolutly not possible with this data.
But maybe you're looking for something like this (SQL Server 2012+):
DECLARE @Sample TABLE ( OrderNo INT, EntriesRemaining FLOAT, NoOfEntries FLOAT, PRIMARY KEY ( OrderNo ) ); INSERT INTO @Sample VALUES ( 1, 100, 175 ), ( 2, 0.856516977, 149.890471 ), ( 3, 0.699488491, 104.8466594 ), ( 4, 0.71297989, 74.75355971 ), ( 5, 0.944871795, 70.63253014 ), ( 6, 0.900949797, 63.63636365 ); SELECT *, LAG( NoOfEntries, 1, NULL ) OVER ( ORDER BY OrderNo ASC), EntriesRemaining * LAG( NoOfEntries, 1, NULL ) OVER ( ORDER BY OrderNo ASC) FROM @Sample;
Monday, February 22, 2016 8:50 PM 
Hi Stefan, Thanks for the quick response, I need to populate the No Of Entries column using the 100% number which in my case is 175, using 175 I get the 85% value of 175 and use that value to get the 69% and so on...Monday, February 22, 2016 9:00 PM

create table Accessinfo (id int identity(1,1), EntriesRemaining decimal(10,2), NoOfEntries decimal(10,2)) Insert into Accessinfo values(1,175) , (0.856516977,149.890471),(0.699488491,104.8466594) ,(0.71297989,74.75355971),(0.944871795,70.63253014) ,(0.900949797, 63.63636365) declare @cnt int=(select count(*) from Accessinfo) ;with mycte as ( SELECT id, EntriesRemaining, Cast(NoOfEntries as decimal(10,2)) as product1 FROM Accessinfo where EntriesRemaining=1 Union all Select a.id, a.EntriesRemaining, Cast(product1*a.EntriesRemaining as decimal(10,2)) as cnt from mycte m join Accessinfo a on a.id=m.id+1 ) select * from mycte order by id drop table Accessinfo
 Proposed as answer by pituachMVP Monday, February 22, 2016 9:36 PM
Monday, February 22, 2016 9:15 PM 
It is not clear how did you get 69% and so on. What do you use to get this 69% (which numbers)?
For every expert, there is an equal and opposite expert.  Becker's Law
My blog
My TechNet articlesMonday, February 22, 2016 9:19 PM 
Assuming that you have a column with all the desired percentages prepopulated,
create table #mytemp (ID INT IDENTITY(1,1),EntriesRemaining numeric(5,2),NoofEntries numeric(5,2)) INSERT INTO #mytemp VALUES(100,175),(0.85,NULL),(0.69,NULL) SELECT * FROM #mytemp where EntriesRemaining=100 UNION select t1.ID,t1.EntriesRemaining ,t1.EntriesRemaining*t2.NoofEntries as NoofEntries FROM #mytemp t1 JOIN #mytemp t2 on t1.ID1=t2.ID where t1.ID>1
Aparna
 Proposed as answer by appsqldev Monday, February 22, 2016 9:41 PM
Monday, February 22, 2016 9:31 PM 
Using Stefan's sample table, here is the solution:
DECLARE @Sample TABLE ( OrderNo INT, EntriesRemaining FLOAT, NoOfEntries FLOAT, PRIMARY KEY ( OrderNo ) );
INSERT INTO @Sample
VALUES ( 1, 100, 175 ),
( 2, 0.856516977, 149.890471 ),
( 3, 0.699488491, 104.8466594 ),
( 4, 0.71297989, 74.75355971 ),
( 5, 0.944871795, 70.63253014 ), ( 6, 0.900949797, 63.63636365 );SELECT *, 175 * power(1E1, SUM(LOG10(EntriesRemaining)) OVER (ORDER BY OrderNo ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))
FROM @Sample
WHERE OrderNo > 1The problem is a running total. There is no product aggregator in SQL Server. However, as long as all values in the product are positive, we can use the fact that a*b = n^(logn(a) + logn(b)), which is what I use here. (With n = 10.)
The solution requires SQL 2012 so that we can use the window aggregate.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
 Proposed as answer by Naomi N Monday, February 22, 2016 11:19 PM
 Marked as answer by Sam ZhaMicrosoft contingent staff Friday, March 4, 2016 8:54 AM
Monday, February 22, 2016 11:15 PM