Link to home
Start Free TrialLog in
Avatar of AGION
AGION

asked on

Making a running total

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?

 

Avatar of dqmq
dqmq
Flag of United States of America image

Please, do you have an Order By?
Avatar of AGION
AGION

ASKER

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
Avatar of chapmandew
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
ASKER CERTIFIED SOLUTION
Avatar of dqmq
dqmq
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial