• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 206
  • Last Modified:

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
0
tvae
Asked:
tvae
  • 5
  • 3
  • 2
  • +1
1 Solution
 
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now