Sql-server – Subtracting sum of previous rows

running-totalssql serversql-server-2008sql-server-2008-r2

I want to create total field in this table. But in SQL server 2008 can't use 'order by' in OVER clause.

I have same PO and difference INV. I want to Qty field minus Quantity field and next row Total field minus Quantity field.
This is a structure table.

enter image description here

And this's a result.

enter image description here

This is my code but it's error at ORDER BY.

select t2.BaseRef 'PO',t2.BaseQty 'qty', t1.NumAtCard 'INV',
        t2.Quantity 'Quantity',
        t2.BaseQty - SUM(t2.Quantity) OVER (ORDER BY t1.DocDate) AS 'total'
from OPDN t1 
        INNER JOIN PDN1 t2 ON t1.DocEntry = t2.DocEntry

Can I use other way for sort this data?

Best Answer

If SUM() OVER() is not supported, you may try with the following approach. Rows are ordered by INV number as text (INV100, INV105, ...), not as number (100, 105, ...).

Input:

CREATE TABLE #PO (
    PO varchar(10),
    DocEntry int,
    Qty int
)
CREATE TABLE #INV (
    INV varchar(10),
    DocEntry int,
    Qty int
)
INSERT INTO #PO (PO, DocEntry, Qty) VALUES ('A123', 1, 500)
INSERT INTO #PO (PO, DocEntry, Qty) VALUES ('B123', 2, 200)
INSERT INTO #INV (INV, DocEntry, Qty) VALUES ('IV100', 1, 100)
INSERT INTO #INV (INV, DocEntry, Qty) VALUES ('IV102', 2, 10)
INSERT INTO #INV (INV, DocEntry, Qty) VALUES ('IV105', 1, 200)
INSERT INTO #INV (INV, DocEntry, Qty) VALUES ('IV106', 2, 190)
INSERT INTO #INV (INV, DocEntry, Qty) VALUES ('IV112', 1, 200)

Statement:

SELECT 
    p.PO, 
    p.Qty,
    i.INV,
    i.Qty AS Quantity,
    Total = p.Qty - 
            (
             SELECT SUM(rt.Qty) 
             FROM #INV rt 
             WHERE (rt.DocEntry = i.DocEntry) AND (rt.INV <= i.INV)
            )
FROM #PO p
LEFT JOIN #INV i ON (p.DocEntry = i.DocEntry)
ORDER BY p.DocEntry, i.INV

Output:

PO      Qty INV     Quantity    Total
A123    500 IV100   100         400
A123    500 IV105   200         200
A123    500 IV112   200         0
B123    200 IV102   10          190
B123    200 IV106   190         0
Related Question