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.

Answer 1

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;

Answer 2

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

Answer 3

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

Answer 4

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 articles

Answer 5

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.ID-1=t2.ID 
	where t1.ID>1

---

Aparna

Answer 6

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 > 1

The 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, [e-mail]