Solved

Rolling sum

Posted on 2013-11-23
11
197 Views
Last Modified: 2014-04-03
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
0
Comment
Question by:tvae
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39671907
Please post the query you have so far
0
 

Author Comment

by:tvae
ID: 39672006
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
 
LVL 40

Expert Comment

by:Sharath
ID: 39672227
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
 

Author Comment

by:tvae
ID: 39672612
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
 
LVL 32

Expert Comment

by:awking00
ID: 39674409
What version of sql server are you using and what determines the order of your rows?
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Comment

by:tvae
ID: 39675354
2005 and determined by ItemNumber,Stk,Bin order by TransactionDate
0
 
LVL 32

Expert Comment

by:awking00
ID: 39675561
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
 

Author Comment

by:tvae
ID: 39675734
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
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 39679996
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
 

Author Comment

by:tvae
ID: 39681164
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
 
LVL 40

Expert Comment

by:Sharath
ID: 39682446
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now