SQLTeam.com | Weblogs | Forums

TSQL Inventory remaning balance

sql2008r2

#1

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

#2

--- 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.


#3

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.


#4

UNBOUNDED PRECEDING needs SQL 2012


#5

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


#6

Thank you very much klllmmm.


#7

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


#8

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

#9

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.


#10

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.