INSERT INTO [TEST].[dbo].[tb5] VALUES
('P0079543','L1022',6,'2016-11',330,72),
('P0079543','L1022',6,'2016-12',330,132),
('P0079543','L1022',6,'2017-01',330,126)
---- Code to get the running total
select t5.PurhcaseOrder, t5.itemnumber ,t5.linenum ,t5.received_month , t5.delivery_qty,
sum(t5.delivery_qty) over(order by t5.received_month rows unbounded preceding) as Delivery_runningQty
,(330- (sum(t5.delivery_qty) over(order by t5.received_month rows unbounded preceding))) as Order_remainingQty
from [TEST].[dbo].[tb5] t5
Hi klllmmm, thanks for the reply.
I tried your codes and I'm getting an error say incorrect syntax near rows and this code "rows unbounded " was underlined by red color. By the way, I'm using SQL 2008 R2.
Sorry abt this. I shall read the tags carefully.
Pls check the below code
--- Code to get running total
select
t5.PurhcaseOrder, t5.itemnumber ,t5.linenum ,t5.received_month , t5.delivery_qty,
rt.Delivery_runningQty,
(330-(rt.Delivery_runningQty)) 'Order_remainingQty'
from [TEST].[dbo].[tb5] t5
cross apply (select sum(delivery_qty) as Delivery_runningQty
from [TEST].[dbo].[tb5]
where received_month <= t5.received_month
) as rt
order by t5.received_month
I tried this another solution when I run this script I'm getting an error "Incorrect syntax near "Order"
select
t.PurhcaseOrder ,
t.itemnumber ,
t.linenum ,
t.received_month ,
t.po_qty ,
t.delivery_qty,
t.po_qty-sum(t.delivery_qty) OVER (PARTITION BY PurhcaseOrder, itemNumber ORDER BY received_month) as Balance_qty
from @sample t
Careful... Queries such as that are based on an "inequality join" known as a "Triangular Join" and are basically 1/2 of a full (square) Cartesian Product. Internally, they produce (N^2 + N)/2 rows and are a huge waste of memory I/O and CPU. Unless you get very lucky with the correct indexing, their performance is quite bad and gets worse at an "O^2" rate.
In 2008, you cannot use ORDER BY in conjunction with an aggregate function. Here's the syntax from Books Online...
Ranking Window Functions
< OVER_CLAUSE > :: =
OVER ( [ PARTITION BY value_expression , ... [ n ] ]
)
Aggregate Window Functions
< OVER_CLAUSE > :: =
OVER ( [ PARTITION BY value_expression , ... [ n ] ] )
There's a way to do some very high speed, low overhead, low CPU usage running totals in 2008 (over a million rows in a couple of seconds). The question is, are you allowed to use Temp Tables because that's a part of what will be needed.