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.PO R1.ShipDat e))
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.RD R1.ShipDat e))
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?
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.PO
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.RD
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?
Please, do you have an Order By?
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
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
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
http://articles.techrepublic.com.com/5100-10878_11-6100447.html
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.