SQLTeam.com | Weblogs | Forums

TSQL Inventory remaning balance

Hi,

Could not figured out on how to get the remaining balance of first row and the remaining balance of first row will be as beg balance of next row.

330-72 = 258 remaining balance
258-132 =126
126-126 =0

please see below sample ddl and sample result.

declare @sample table
(PurhcaseOrder nvarchar(20),
itemnumber nvarchar(20),
linenum int,
received_month nvarchar(10),
po_qty int,
delivery_qty int
)



insert @sample (PurhcaseOrder,itemnumber,linenum,received_month,po_qty,delivery_qty)
VALUES
('P0079543','L1022',6,'2016-11',330,72),
('P0079543','L1022',6,'2016-12',330,132),	
('P0079543','L1022',6,'2017-01',330,126)	

select 
		PurhcaseOrder ,
		itemnumber ,
		linenum ,
		received_month ,
		po_qty ,
		delivery_qty,
		remaining_bal=0
 from @sample



 
PurchaseOrder|ItemNumber|Linenumber|Received_Month|PO_Qty|Delivered_Qty|Remaining
----------------------------------------------------------------------------------
PH0079543-----APL1022--------6------2016-11--------330-------72------------258
PH0079543-----APL1022--------6------2016-12--------330-------132-----------126
PH0079543-----APL1022--------6------2017-01--------330-------126-----------0

--- Insert the values
CREATE TABLE [TEST].[dbo].[tb5]
(PurhcaseOrder NVARCHAR(55), itemnumber NVARCHAR(55), linenum FLOAT, received_month NVARCHAR(55),po_qty FLOAT, delivery_qty FLOAT)

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

Hope this is what you expected.

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.

UNBOUNDED PRECEDING needs SQL 2012

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

Thank you very much klllmmm.

1 Like

Hi Kristen, I run the script in SQL 2012 and I'm getting also that error using the first solution given by klllmmm.

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.