Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Rolling sum

Posted on 2013-11-23
11
200 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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
 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

809 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