Solved

Making a running total

Posted on 2008-10-10
4
295 Views
Last Modified: 2012-08-14
I've got the following query:

SELECT     TOP (100) PERCENT dbo.OPOR.DocNum, 'Inkooporder' AS DocType, dbo.OPOR.CardCode, dbo.OPOR.CardName, dbo.POR1.OpenQty AS [Te Ontvangen], NULL AS [Te Leveren], Convert(DateTime, CONVERT(VarChar(32),dbo.POR1.ShipDate))
FROM         dbo.OPOR INNER JOIN
                     dbo.POR1 ON dbo.OPOR.DocEntry = dbo.POR1.DocEntry
WHERE     (dbo.POR1.OpenQty > 0) AND dbo.POR1.ItemCode = '6004005'

UNION ALL

SELECT     TOP (100) PERCENT dbo.ORDR.DocNum, 'Verkooporder' AS DocType, dbo.ORDR.CardCode, dbo.ORDR.CardName, NULL AS [Te Ontvangen],  dbo.RDR1.OpenQty AS [Te Leveren], Convert(DateTime, CONVERT(VarChar(32),dbo.RDR1.ShipDate))

FROM         dbo.ORDR INNER JOIN
                      dbo.RDR1 ON dbo.ORDR.DocEntry = dbo.RDR1.DocEntry
WHERE     (dbo.RDR1.OpenQty > 0) AND dbo.RDR1.ItemCode = '6004005'

The result is an overview of item deliveries and items to deliver. Now I would like to have a running total that will take the on hand quantity (OITM.Onhand) as base and then decrease that amount with dbo.RDR1.OpenQty AS [Te Leveren] and increase that amount  with  dbo.POR1.OpenQty AS [Te Ontvangen]. How should the query be?

 

0
Comment
Question by:AGION
  • 2
4 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 22684958
Please, do you have an Order By?
0
 

Author Comment

by:AGION
ID: 22684973
My actual query include Grtoup by;
 

SELECT dbo.OPOR.DocNum, 'Inkooporder' AS DocType, dbo.OPOR.CardCode, dbo.OPOR.CardName, dbo.POR1.OpenQty AS [Te Ontvangen], NULL AS [Te Leveren],
CONVERT(DateTime, CONVERT(VarChar(32), dbo.POR1.ShipDate)) AS Leverdatum
FROM dbo.OPOR INNER JOIN
dbo.POR1 ON dbo.OPOR.DocEntry = dbo.POR1.DocEntry INNER JOIN
dbo.OITM ON dbo.POR1.ItemCode = dbo.OITM.ItemCode
WHERE (dbo.POR1.OpenQty > 0) AND (dbo.POR1.ItemCode = '6004005')
UNION ALL
SELECT TOP (100) PERCENT dbo.ORDR.DocNum, ' Verkooporder ' AS DocType, dbo.ORDR.CardCode, dbo.ORDR.CardName, NULL AS [Te Ontvangen],
dbo.RDR1.OpenQty AS [Te Leveren], CONVERT(DateTime, CONVERT(VarChar(32), dbo.RDR1.ShipDate)) AS Leverdatum
FROM dbo.ORDR INNER JOIN
dbo.RDR1 ON dbo.ORDR.DocEntry = dbo.RDR1.DocEntry INNER JOIN
dbo.OITM ON dbo.RDR1.ItemCode = dbo.OITM.ItemCode
WHERE (dbo.RDR1.OpenQty > 0) AND (dbo.RDR1.ItemCode = '6004005')
ORDER BY Leverdatum
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22686033
check this article out...will give you an idea as to what you'll need to do

http://articles.techrepublic.com.com/5100-10878_11-6100447.html
0
 
LVL 42

Accepted Solution

by:
dqmq earned 500 total points
ID: 22687681
In order to do this, we need to have an order by that determines the sequence of rows in the output with no two rows having the same order by value(s).  Without that, there it is not possible to determine which row rolls forward to the next.   So, assuming leverdatum is such a column....

This probably has some typos, but it should give a goo idea of the form you need.  Also, I'm still a little unsure about the Ordering, so you may need to tweak that a bit  


;with myCTE as
(SELECT dbo.OPOR.DocNum, 'Inkooporder' AS DocType, dbo.OPOR.CardCode, dbo.OPOR.CardName, dbo.POR1.OpenQty AS [Te Ontvangen], NULL AS [Te Leveren],
CONVERT(DateTime, CONVERT(VarChar(32), dbo.POR1.ShipDate)) AS Leverdatum
FROM dbo.OPOR INNER JOIN
dbo.POR1 ON dbo.OPOR.DocEntry = dbo.POR1.DocEntry INNER JOIN
dbo.OITM ON dbo.POR1.ItemCode = dbo.OITM.ItemCode
WHERE (dbo.POR1.OpenQty > 0) AND (dbo.POR1.ItemCode = '6004005')
UNION ALL
SELECT TOP (100) PERCENT dbo.ORDR.DocNum, ' Verkooporder ' AS DocType, dbo.ORDR.CardCode, dbo.ORDR.CardName, NULL AS [Te Ontvangen],
dbo.RDR1.OpenQty AS [Te Leveren], CONVERT(DateTime, CONVERT(VarChar(32), dbo.RDR1.ShipDate)) AS Leverdatum
FROM dbo.ORDR INNER JOIN
dbo.RDR1 ON dbo.ORDR.DocEntry = dbo.RDR1.DocEntry INNER JOIN
dbo.OITM ON dbo.RDR1.ItemCode = dbo.OITM.ItemCode
WHERE (dbo.RDR1.OpenQty > 0) AND (dbo.RDR1.ItemCode = '6004005')
)
Select *,
(
Select OITM.Onhand + sum([Te Ontvangen]) - sum([Te Leveren])
from MyCTE as S, dbo.ORDERITEM OITM  ON OITM.ItemCode = myCTE.ItemCode
where myCTE.Leverdatum <= S.LeverDatum
) as RunningTotal
FROM MyCTE
ORDER BY Leverdatum asc

0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

912 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

20 Experts available now in Live!

Get 1:1 Help Now