Rolling sum

I have a SQL query that has the total on hand qty and another column that has a qty that I need to subtract from the on hand until it all get consumed .

Ex:
On hand = 50
Column qty
10
15
10
20

So I need a column that will have
40 (from 50-10)
25 ( from 40 - 15)
15 ( from 25-10)
-5 ( from 15-20)

How can I accomplish this using transact sql
tvaeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Patrick MatthewsCommented:
Please post the query you have so far
0
 
tvaeAuthor Commented:
Select TransactionDate, OnHandQty,Stk,Bin,MovedQty
From TblItemInventory I join
tblItemInventoryHistory H on I.ItemKey = H.ItemKey
Order By TransactionDate Desc


What I am looking for is I need to get the transactions in the History table that makes the OnHandQty.
0
 
SharathData EngineerCommented:
Post the result of your query. Do you have 50 in OnHandQty column and want to have another column in your query?
Better post the result and expected result.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
tvaeAuthor Commented:
Results
On hand  move qty results.   Or result
50             10            40.            10
50.             15.           25.           25
50.              10.          15.           35
50.              20.          -5.           55
0
 
awking00Commented:
What version of sql server are you using and what determines the order of your rows?
0
 
tvaeAuthor Commented:
2005 and determined by ItemNumber,Stk,Bin order by TransactionDate
0
 
awking00Commented:
I was hoping you had the latest version that includes lead and lag functions, which make this kind of query much easier. At any rate, can you post some sample data for the two tables (and which columns belong to which table) that would produce your sample desired results?
0
 
tvaeAuthor Commented:
Here's my actual query and results. The Quantity field is what I need a running total for the ItemNumber.Stk/Bin Combination. or A running field that deducts Inventory - Quantity

Select TransactionDate, h.ItemNumber,h.TransactionFunctionCode,
Stockroom1,
Bin1,
i.InventoryCategory,
i.InventoryQuantity,
case when ActionCode = 'X' then ReversedQuantity1 *-1 else MoveQuantity1 end Quantity
from  dbo.FS_ItemInventory i  left join
dbo.FS_ItemInventoryHistory h on i.ItemKey = h.ItemKey and Stockroom = Stockroom1 and Bin = Bin1 and InventoryCategory = InventoryCategory1
Where TransactionFunctionCode in ('PORV','MORV') and InventoryCategory in ('O','H') and ItemNumber ='0000102'
Order By h.ItemNumber,Stockroom,Bin,i.InventoryCategory,h.TransactionDate desc

Results
   
TransactionDate                 Itemnumber      TRANS      STK          BIN             Category      Onhand      Quantity
2013-11-14 00:00:00.000      0000102      MORV      MI          C7503             O      4      1
2013-09-09 00:00:00.000      0000102      PORV      MI      C7503      O      4      24
2013-07-31 00:00:00.000      0000102      PORV      MI      C7503      O      4      -36
2013-07-03 00:00:00.000      0000102      PORV      MI      C7503      O      4      36
2013-07-03 00:00:00.000      0000102      PORV      MI      C7503      O      4      36
2013-01-29 00:00:00.000      0000102      PORV      MI      C7503      O      4      12
2013-01-29 00:00:00.000      0000102      PORV      MI      C7503      O      4      12
2013-01-29 00:00:00.000      0000102      PORV      MI      C7503      O      4      12
0
 
SharathData EngineerCommented:
This is an approach to find the rolling sum.
;WITH CTE AS (
SELECT *,
       (SELECT SUM(Move) 
          FROM Test t2 
         WHERE t2.Transaction_Date <= t1.Transaction_Date) Result
  FROM Test t1)
SELECT *,On_Hand-Result Qty
  FROM CTE

Open in new window


http://sqlfiddle.com/#!3/418f1/3
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
tvaeAuthor Commented:
Sharath_123,

This works if I am summing every record, I need it to sum only by grouping it with itemnumber, stockroom and bin combination

How can I add to that?
Thanks
0
 
SharathData EngineerCommented:
You need to add them to WHERE clause of sub-query like this.
;WITH CTE AS (
SELECT *,
       (SELECT SUM(Move) 
          FROM Test t2 
         WHERE t2.Transaction_Date <= t1.Transaction_Date
		   AND t1.itemnumber = t2.itemnumber
		   AND t1.stockroom = t2.stockroom
		   AND t1.bin = t2.bin) Result
  FROM Test t1)
SELECT *,On_Hand-Result Qty
  FROM CTE

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.