Subtracting sum of previous rows
1
vote
2
answers
4317
views
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.
And this's a result.
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?
And this's a result.
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?
Asked by midtonight
(11 rep)
Mar 7, 2019, 05:41 AM
Last activity: May 26, 2021, 01:00 PM
Last activity: May 26, 2021, 01:00 PM