[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Rolling sum

Posted on 2013-11-23
11
Medium Priority
?
205 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
[X]
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
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 93

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 41

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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

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 41

Accepted Solution

by:
Sharath earned 2000 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 41

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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

650 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