Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Making a running total

Posted on 2008-10-10
Medium Priority
302 Views
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
Question by:AGION
[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
• 2

LVL 42

Expert Comment

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

Author Comment

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

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

dqmq earned 2000 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

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
###### Suggested Courses
Course of the Month7 days, 14 hours left to enroll