Solved

Making a running total

Posted on 2008-10-10
4
300 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
[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
  • 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

615 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