How to calculate a cumulative value ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to calculate a cumulative value ?

Author

Topic

paulnamroud
Starting Member

26 Posts

Posted - 2012-06-28 : 11:55:05

Hello,

I have a transaction table that contains 3 columns:
- Date
- Item #
- Quantity Sold

So, I would like to know how can I build a SQL Statement that calculate the cumulative value of a "Quantity Sold / Total Quantity Sold" by Date & Item # as shown below!

Date Item # Quantity Sold Cumulative Quantity Sold (--> This Column that I want)
----------------------------------------------------------------------------------------------------
2012-06-01 101 10 10 (= 0 + 10)
2012-06-02 101 15 25 (= 10 + 15)
2012-06-03 101 100 125 (= 10 + 15 + 100)
2012-06-04 101 225 350 (= 10 + 15 + 100 + 225)

2012-06-05 102 50 50 (= 0 + 50)
2012-06-06 102 25 75 (= 50 + 25)

2012-06-07 103 100 100 (= 0 + 100)

2012-06-08 101 50 400 (= 10 + 15 + 100 + 225 + 50)

2012-06-08 102 100 175 (= 50 + 25 + 100)

2012-06-12 101 20 420 (= 10 + 15 + 100 + 225 + 50 + 20)

2012-06-14 101 40 460 (= 10 + 15 + 100 + 225 + 50 + 20 + 40)

2012-06-14 104 70 70 (= 0 + 70)

2012-06-18 103 80 180 (= 0 + 100 + 80)

2012-06-22 101 -140 320 (= 10 + 15 + 100 + 225 + 50 + 20 + 40 -140) -> Credit
2012-06-22 103 400 580 (= 0 + 100 + 80 + 400)
2012-06-22 102 -22 153 (= 50 + 25 + 100 - 22) -> Credit

Thank you

Paul

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-28 : 12:33:28

If that's all you want then
declare @t table (dte datetime, id int, q money)

insert @t select '20010101', 1, 10
insert @t select '20010102', 1, 20
insert @t select '20010103', 1, 30

select id, dte, q, qtot = (select SUM(q) from @t t2 where t2.id = t1.id and t2.dte <= t1.dte) from @t t1

But you mention Quantity Sold / Total Quantity Sold so
declare @t table (dte datetime, id int, q money)

insert @t select '20010101', 1, 10
insert @t select '20010102', 1, 20
insert @t select '20010103', 1, 30

;with t1 as (select id, q = SUM(q) from @t group by id)
, t2 as (select id, dte, q, qtot = (select SUM(q) from @t t2 where t2.id = t1.id and t2.dte <= t1.dte) from @t t1)
select t2.dte, t2.q, t2.q/t1.q
from t1
join t2 on t1.id = t1.id

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-28 : 16:00:03

see scenario 1

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources