|
paulnamroud
Starting Member
24 Posts |
 Posted - 06/28/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 06/28/2012 : 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. |
 |
|
How to calculate a cumulative value ?
