Cumulative Sum
-
Apologies if this may appear to be a ridiculous question as I am still coming to grips with databases but is it possible to calculate a cumulative sum?
I have a table with inventory and consumption volumes in one column (consumption being negative) over a range of dates and need to be able to calculate the total inventory on every given date using sum of the previous values.
Any help would be appreciated
-
Hello Lee,
Can you provide a data sample? It kinda sounds like
ID Desc Qty Dt
533 someone 100 05/01/2006 12:15:13.000
534 notone (15) 05/01/2006 12:16:13.000
535 otherone 150 05/02/2006 2:15:13.000
536 someone (160) 05/03/2006 17:15:13.000
and that you want an aggregate accounting (per day?) of qty on hand - correct?
- J
-
It's basically an inventory control/forecasting process. The data you gave is correct but I do not know how to produce the data on a daily basis.
I have a web application, which simply gives the inventory based on a date provided by the user however it needs to be more advanced since it needs to be able to show dates where the levels drop below minimum.
-
Hey Lee, Sorry for the delay.
Here is my solution. It may not be the best but it works well without creating any other objects (i.e. temp table or table var). Turns out that this was a little more fun that it first appeared to be.
I increased the # of sample records for better range testing ...
--DROP TABLE #_AggPlay
--CREATE TABLE #_AggPlay (ID INT, Description VARCHAR (20), Qty INT, Dt DATETIME)
--INSERT #_AggPlay (ID, Description, Qty, Dt)
--SELECT 533, 'someone', 100, '05/01/2006 12:15:13.000'
--UNION SELECT 534, 'notone' , -15, '05/01/2006 12:16:13.000'
--UNION SELECT 535 , 'otherone' , 150 , '05/02/2006 2:15:13.000'
--UNION SELECT 536 , 'someone' , -160 , '05/03/2006 17:15:13.000'
--UNION SELECT 537 , 'omeone' , -60 , '05/03/2006 17:15:13.000'
--UNION SELECT 538 , 'meone' , 130 , '05/04/2006 17:15:13.000'
--UNION SELECT 539 , 'eone' , 250 , '05/04/2006 17:15:13.000'
--UNION SELECT 540 , 'one' , -19 , '05/04/2006 17:15:13.000'
--UNION SELECT 541 , 'someon' , -62 , '05/05/2006 17:15:13.000'
--UNION SELECT 542 , 'someo' , 99 , '05/06/2006 17:15:13.000'
And below is my solution ...
SELECT CONVERT(VARCHAR(12), t1.Dt, 101)
, SUM(t2.Qty)
FROM
(
SELECT DISTINCT CONVERT(VARCHAR(12), Dt, 101) AS Dt
FROM #_AggPlay
) AS t1
INNER JOIN
(
SELECT SUM(Qty) AS Qty
, CONVERT(VARCHAR(12), Dt, 101) AS Dt
FROM #_AggPlay
GROUP BY CONVERT(VARCHAR(12), Dt, 101)
) AS t2
ON t1.Dt >= t2.Dt
GROUP BY CONVERT(VARCHAR(12), t1.Dt, 101)
ORDER BY CONVERT(VARCHAR(12), t1.Dt, 101) ASC
Basically, I am creating two derived tables, one to establish the unique dates (which allows me to then have controlled ranges of values (this is the first part of the FROM clause)) and another to perform the daily aggregates (this is the JOIN object). I then perform another aggregate query on the result of joining the two derived tables that then performs the sum based upon each unique date range.
I hope this is helpful - lemming no if you would like a more detailed explanation or breakdown.
I am curious if others have a better technique???
- J
-
Many thanks for your reply. I am still new to this so I will work through what you gave me and let you know how it goes.
Thanks again
-
Just to let you know your solution worked perfectly. thank you
-
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply