SQL cumulative calculation

1

Hi, I have a result from my multiple CTE scripts but I have a hard time on how to get the cumulative value.
below is a sample ddl and result set.

[code]create table #sample
(sdesc nvarchar(35), qty int, share numeric(12,2), defect numeric(12,2),rownum int)
insert into #sample(sdesc,qty,share,defect,rownum)values('Locked',31407,0.272254440485787,0.298452006499862,1)
insert into #sample(sdesc,qty,share,defect,rownum)values('Corroded',8590,0.074463197496510,0.081628386532741,2)
insert into #sample(sdesc,qty,share,defect,rownum)values('power on',5543,0.048050000433429,0.052673590983816,3)
insert into #sample(sdesc,qty,share,defect,rownum)values('high current',4490,0.038921974011563,0.042667224159721,4)
insert into #sample(sdesc,qty,share,defect,rownum)values('Failure',3783,0.032793280108184,0.035948799331008,5)
insert into #sample(sdesc,qty,share,defect,rownum)values('low current',2841,0.024627467297740,0.026997234707743,6)

SAMPLE DESIRED RESULT

sdesc-------------qty----share %-------------defect %==========RowNum--new column percentage

Locked-----------31407--0.272254440485787---0.298452006499862---1-----0.272254440485787
Corroded---------8590---0.074463197496510---0.081628386532741---2-----0.074463197496510+0.272254440485787
power on---------5543---0.048050000433429---0.052673590983816---3-----0.048050000433429+0.074463197496510
high current-----4490---0.038921974011563---0.042667224159721---4-----0.038921974011563+0.048050000433429
Failure----------3783---0.032793280108184---0.035948799331008---5-----0.032793280108184+0.038921974011563
low current------2841---0.024627467297740---0.026997234707743---6-----0.024627467297740+0.032793280108184[/code]

2

This is my final query and also get the cumulative share %. Anyway, may I ask your help guys on how to optimize this query. thanks.

[code]declare @begdate datetime
declare @enddate datetime
set @begdate ='2016-4-1'
set @enddate ='2016-5-1'

;with cteAll as
(
select , row_number() over (partition by po_number order by po_number) as rnk
from dbo.ods_cores
where po_ended >= @begdate and po_ended < @enddate
)
, cte_final as
(
select sdesc, qty, cast(qty as decimal(12,2)) / cast(total_vol as decimal(12,2)) as [defect %],
cast(qty as decimal(12,2)) / cast(sum(qty) over() as decimal(12,2)) as [share %],
sum(qty) over() as total_qty,
total_vol, row_number() over (order by [qty] DESC) RowNum
from
(
select def.sdesc , COUNT() as Qty,
(select count(*) from cteAll vol where rnk=1) as total_vol

from cteAll def
where asustatus =6
group by def.sdesc
)t
)
select a.sdesc, a.qty, SUM(b.[share %]) as [share %], a.[defect %]
from cte_final a
inner join cte_final as b
on b.RowNum <= a.RowNum
group by a.sdesc, a.qty, a.[share %], a.[defect %], a.RowNum
order by Qty desc[/code]

3 
select *, 
	share + lag(share,1,0) over(order by RowNum)  as NewCol
from #sample;
4

I did not understand this second query you posted - it uses your actual tables rather than your sample data.

Your sample result in the first post seems to indicate that you are looking for the sum of the share for the current row + share for the previous row. The code I posted will do that (if you are on SQL 2012 or later).

Usually when people refer to "Cumulative calculation" or "Running total", they mean something else. They mean to say that they want to calculate the sum of values of some column from all the previous rows. Is that what you are looking for?

5

Yes JamesK. I have have to sum the share for the first row plus share for the previous row. I don't have sql 2012. the one install in my pc is sql 2008 r2. the problem with my query specially last script it takes longer times to process. 5014 records but the time consume is 4 minutes and 21 seconds.

6

I'm worrying if the data is huge lets say 1 month data I think this will take time to process.. by the way, I'm using SQL 2008 R2.