Rolling balance to carry to next month

I have a query that get the balance for last month and current month balance. I'm using a function inside the view that took more than 30 minutes. May I ask your help guys any idea or approach to speed up the processing or optimize the script. thank you.

below is a sample data and actual result.

[code]create table #oemneed (po_month nvarchar(10), item nvarchar(10), oemneed_qty int)
insert #oemneed values ('2016-08','M4305',0)
insert #oemneed values ('2016-08','M4305',17)
insert #oemneed values ('2016-08','M4305',50)
insert #oemneed values ('2016-08','M4305',67)
insert #oemneed values ('2016-08','M4305',860)
insert #oemneed values ('2016-08','M4305',975)
insert #oemneed values ('2016-08','M4305',2000)
insert #oemneed values ('2016-08','M4317',0)
insert #oemneed values ('2016-08','M4317',17)
insert #oemneed values ('2016-08','M4317',50)
insert #oemneed values ('2016-08','M4317',67)
insert #oemneed values ('2016-08','M4317',860)
insert #oemneed values ('2016-08','M4317',975)
insert #oemneed values ('2016-08','M4317',2154)
insert #oemneed values ('2016-08','A1003',11)
insert #oemneed values ('2016-08','A1003',19)
insert #oemneed values ('2016-08','A1003',48)
insert #oemneed values ('2016-08','A1003',71)
insert #oemneed values ('2016-09','M4305',0)
insert #oemneed values ('2016-09','M4305',55)
insert #oemneed values ('2016-09','M4305',67)
insert #oemneed values ('2016-09','M4305',85)
insert #oemneed values ('2016-09','M4305',125)
insert #oemneed values ('2016-09','M4305',300)
insert #oemneed values ('2016-09','M4305',451)
insert #oemneed values ('2016-09','M4305',519)
insert #oemneed values ('2016-09','M4317',55)
insert #oemneed values ('2016-09','M4317',67)
insert #oemneed values ('2016-09','M4317',85)
insert #oemneed values ('2016-09','M4317',125)
insert #oemneed values ('2016-09','M4317',308)
insert #oemneed values ('2016-09','M4317',451)
insert #oemneed values ('2016-09','M4317',519)
insert #oemneed values ('2016-09','A1003',17)
insert #oemneed values ('2016-09','A1003',18)
insert #oemneed values ('2016-09','A1003',19)
insert #oemneed values ('2016-09','A1003',23)
insert #oemneed values ('2016-09','A1003',45)

create table #onhand (oh_month nvarchar(10), item nvarchar(20),R int, IR int, S int, RB int, OEM int )
insert #onhand values ('2016-09','M4305',771,0,0,0,0)
insert #onhand values ('2016-09','M4317',0,0,0,0,16)
insert #onhand values ('2016-09','M4317',2552,0,0,0,0)
insert #onhand values ('2016-09','A1003',0,0,0,0,31081)
insert #onhand values ('2016-09','A1003',0,385,0,0,0)
insert #onhand values ('2016-09','A1003',46,0,0,0,0)
insert #onhand values ('2016-09','A1003',0,0,5,0,0)

create table #openorder (del_month nvarchar(10), item nvarchar(20),S int, RB int,RG int, OEM int, GEN int )
insert #openorder values ('2016-09','A1003',0,0,0,10345,0)

/*** CREATE VIEW [dbo].[vw_Order_Qty_PerMo] *****/

select
t.po_month

  /*** identity the next month date ***/
  ,SUBSTRING(CAST(CONVERT(date,DATEADD(month,1,CONVERT(datetime,SUBSTRING(t.po_month,1,4)+SUBSTRING(t.po_month,6,2)+'01'))) AS VARCHAR),1,7) AS next_month


  ,t.item
  
  ,CASE WHEN OEM_Need - (OnHand_R + OnHand_RB + OnHand_S + OnHand_OEM+ OnOrder_RG + OnOrder_RB + OnOrder_S + OnOrder_OEM) - balance_lm < 0 THEN 0 
              ELSE OEM_Need - (OnHand_R + OnHand_RB + OnHand_S + OnHand_OEM+ OnOrder_RG + OnOrder_RB + OnOrder_S + OnOrder_OEM )- balance_lm 
        END AS Order_Qty_1
  
  ,CASE WHEN (OEM_Need - (OnHand_R + OnHand_RB + OnHand_S + OnHand_OEM+ OnOrder_RG + OnOrder_RB + OnOrder_S + OnOrder_OEM)- balance_lm) >= 0 THEN 0 
              ELSE (OEM_Need - (OnHand_R + OnHand_RB + OnHand_S + OnHand_OEM+ OnOrder_RG + OnOrder_RB + OnOrder_S + OnOrder_OEM )- balance_lm) * -1 
        END AS balance_cm
        
  ,t.balance_lm 
        
  ,t.OEM_Need
                    
  ,t.OnHand_R
  ,t.OnHand_RB
  ,t.OnHand_S
  ,t.OnHand_OEM

  ,t.OnOrder_RG
  ,t.OnOrder_RB
  ,t.OnOrder_S
  ,t.OnOrder_OEM

from
(
select oe.po_month, oe.item,
sum(case when oe.oemneed_qty is null then 0 else oemneed_qty end) as OEM_Need,

	sum(case when oh.R is null then 0 else oh.R end) as OnHand_R,
	sum(case when oh.RB is null then 0 else oh.RB end) as OnHand_RB,
	sum(case when oh.S is null then 0 else oh.S end) as OnHand_S,
	sum(case when oh.oem is null then 0 else oh.oem end) as OnHand_OEM ,  

	sum(case when op.RG is null then 0 else op.RG end) as OnOrder_RG,
	sum(case when op.RB is null then 0 else op.RB end) as OnOrder_RB,
	sum(case when op.S is null then 0 else op.S end) as OnOrder_S,
	sum(case when op.oem is null then 0 else op.oem end) as OnOrder_OEM
	   
    ,dbo.get_carry_over_oem_qty(oen.PO_Month,oen.item) AS balance_lm

from #oemneed oe
inner join #onhand oh
on oe.po_month= oh.oh_month
inner join #openorder op
on oe.po_month= op.del_month
where oe.po_month in ('2016-09')
group by
oe.po_month
,oe.item
)t

ALTER FUNCTION [dbo].[get_carry_over_oem_qty](@po_month as nvarchar(7), @item as nvarchar(50))
RETURNS integer as

BEGIN
declare @carry_over as integer;

SET @carry_over = (select distinct balance_cm from [dbo].[vw_Order_Qty_PerMo] lm
where ltrim(lm.next_month) = @po_month
and lm.item = @item)

RETURN (CASE WHEN @carry_over IS NULL THEN 0 ELSE @carry_over END )
END [/code]

Here is the sample actual generated result.

po_month---next_month--item---Order_Qty_1--balance_cm---balance_lm---OEM_Need---OnHand_R------OnHand_RB---OnHand_S---OnHand_OEM---OnOrder_RG--OnOrder_RB--OnOrder_S---OnOrder_OEM 2016-09----2016-10-----A1003----0----------3467816-------0-----------73860-------1748--------- 0---------190---------1181078-----0-----------0-----------0----------- 2358660 2016-09----2016-10-----M4305----0----------4558----------0-----------1610--------6168--------- 0---------0-----------0-----------0-----------0-----------0----------- 0 2016-09----2016-10-----M4317----0----------17324---------0-----------3220--------20416-------- 0---------0-----------128---------0-----------0-----------0----------- 0 2016-10----2016-11-----A1003----0----------3451424-------3467816-----16392-------0------------ 0---------0-----------0-----------0-----------0-----------0----------- 0 2016-10----2016-11-----M4305----0----------828-----------4558--------3730--------0------------ 0---------0-----------0-----------0-----------0-----------0----------- 0 2016-10----2016-11-----M4317----0----------13594---------17324-------3730--------0------------ 0---------0-----------0-----------0-----------0-----------0----------- 0

Please disregard the other post. I encounter problem when I created this post that result to duplicate post. thanks.

Instead of function use it as CTE and join to the query.

Thank you viggneshwar for you reply. May I ask on how to make this in cte especially getting the balance_lm from the previous balance_cm without using the function.

This is the script i used.

[code]select
t.po_month

  /*** identity the next month date ***/
  ,SUBSTRING(CAST(CONVERT(date,DATEADD(month,1,CONVERT(datetime,SUBSTRING(t.po_month,1,4)+SUBSTRING(t.po_month,6,2)+'01'))) AS VARCHAR),1,7) AS next_month
  ,t.item

/** how can i achive this part in cte ****/
,CASE WHEN OEM_Need - (OnHand_R + OnHand_RB + OnHand_S + OnHand_OEM+ OnOrder_RG + OnOrder_RB + OnOrder_S + OnOrder_OEM) - balance_lm < 0 THEN 0
ELSE OEM_Need - (OnHand_R + OnHand_RB + OnHand_S + OnHand_OEM+ OnOrder_RG + OnOrder_RB + OnOrder_S + OnOrder_OEM )- balance_lm
END AS Order_Qty_1

  ,CASE WHEN (OEM_Need - (OnHand_R + OnHand_RB + OnHand_S + OnHand_OEM+ OnOrder_RG + OnOrder_RB + OnOrder_S + OnOrder_OEM)- balance_lm) >= 0 THEN 0 
              ELSE (OEM_Need - (OnHand_R + OnHand_RB + OnHand_S + OnHand_OEM+ OnOrder_RG + OnOrder_RB + OnOrder_S + OnOrder_OEM )- balance_lm) * -1 
        END AS balance_cm
        
  ,t.balance_lm 

 /**************************************/
        
  ,t.OEM_Need
                    
  ,t.OnHand_R
  ,t.OnHand_RB
  ,t.OnHand_S
  ,t.OnHand_OEM

  ,t.OnOrder_RG
  ,t.OnOrder_RB
  ,t.OnOrder_S
  ,t.OnOrder_OEM
  ,row_number() over (partition by po_month, item order by po_month) as rnk

from
(
select oe.po_month, oe.item,
sum(case when oe.oemneed_qty is null then 0 else oemneed_qty end) as OEM_Need,

	sum(case when oh.R is null then 0 else oh.R end) as OnHand_R,
	sum(case when oh.RB is null then 0 else oh.RB end) as OnHand_RB,
	sum(case when oh.S is null then 0 else oh.S end) as OnHand_S,
	sum(case when oh.oem is null then 0 else oh.oem end) as OnHand_OEM ,  

	sum(case when op.RG is null then 0 else op.RG end) as OnOrder_RG,
	sum(case when op.RB is null then 0 else op.RB end) as OnOrder_RB,
	sum(case when op.S is null then 0 else op.S end) as OnOrder_S,
	sum(case when op.oem is null then 0 else op.oem end) as OnOrder_OEM
	   
    /** this is the function that capture the balance_lm from previous balance_cm***/
    --,dbo.get_carry_over_oem_qty(oen.PO_Month,oen.item) AS balance_lm

from #oemneed oe
inner join #onhand oh
on oe.po_month= oh.oh_month and oe.item=oh.item
inner join #openorder op
on oe.po_month= op.del_month and oe.item=op.item
where oe.po_month >= ('2016-09') and oe.item='A1003'
group by
oe.po_month
,oe.item
)t[/code]

sample data:

[code]create table #oemneed (po_month nvarchar(10), item nvarchar(10), oemneed_qty int)
insert #oemneed values ('2016-09','A1003',11)
insert #oemneed values ('2016-09','A1003',19)
insert #oemneed values ('2016-09','A1003',48)
insert #oemneed values ('2016-09','A1003',71)
insert #oemneed values ('2016-10','A1003',17)
insert #oemneed values ('2016-10','A1003',18)
insert #oemneed values ('2016-10','A1003',19)
insert #oemneed values ('2016-10','A1003',23)
insert #oemneed values ('2016-10','A1003',45)

create table #onhand (oh_month nvarchar(10), item nvarchar(20),R int, IR int, S int, RB int, OEM int )
insert #onhand values ('2016-09','A1003',0,0,0,0,31081)
insert #onhand values ('2016-09','A1003',0,385,0,0,0)
insert #onhand values ('2016-09','A1003',46,0,0,0,0)
insert #onhand values ('2016-09','A1003',0,0,5,0,0)
insert #onhand values ('2016-10','A1003',0,0,0,0,2000)
insert #onhand values ('2016-10','A1003',0,38,5,0,0)
insert #onhand values ('2016-10','A1003',40,0,0,0,0)
insert #onhand values ('2016-10','A1003',0,10,5,0,0)

create table #openorder (del_month nvarchar(10), item nvarchar(20),S int, RB int,RG int, OEM int, GEN int )
insert #openorder values ('2016-09','A1003',0,0,0,10345,0)
insert #openorder values ('2016-10','A1003',0,0,0,1000,0)[/code]

sample result:

formula:
--balance_cm--balance of current month
--balance_lm--balance of last month which is came from the balance_cm of previous month.

  ,CASE WHEN (OEM_Need - (OnHand_R + OnHand_RB + OnHand_S + OnHand_OEM+ OnOrder_RG + OnOrder_RB + OnOrder_S + OnOrder_OEM)- balance_lm) >= 0 THEN 0 
              ELSE (OEM_Need - (OnHand_R + OnHand_RB + OnHand_S + OnHand_OEM+ OnOrder_RG + OnOrder_RB + OnOrder_S + OnOrder_OEM )- balance_lm) * -1 
        END AS balance_cm

po_month---next_month--item---Order_Qty_1--balance_cm---balance_lm---OEM_Need---OnHand_R------OnHand_RB---OnHand_S---OnHand_OEM---OnOrder_RG--OnOrder_RB--OnOrder_S---OnOrder_OEM 2016-09----2016-10-----A1003----0-----------413434-------0-------------596---------184--------------0---------20---------124324-------0-----------0-----------0---------165520 2016-10----2016-11-----A1003----0-----------153694-------413434--------488---------200--------------0---------50---------10000--------0-----------0-----------0---------20000

Hi Guys, can you please help me on how to get the desired result or is there any approach. could not figure out make this complicated scenario. thanks in advance.

;with cte_need as
(select po_month, item,DENSE_RANK() over (order by po_month) as rno, SUM(oemneed_qty) as OEM_Need
from #oemneed
group by po_month, item
),
cte_open as
(select del_month, item, SUM(RG) as OnOrder_RG, SUM(RB) as OnOrder_RB, SUM(S) as OnOrder_S, SUM(OEM) as OnOrder_OEM
from #openorder
group by del_month, item
),
cte_hand as
(select oh_month, item, SUM(R) as OnHand_R, SUM(RB) as OnHand_RB, SUM(S) as OnHand_S, SUM(OEM) as OnHand_OEM
from #onhand
group by oh_month, item
),
cte_result as
( select a.po_month ,a.item ,a.rno ,(OEM_Need - (OnHand_R + OnHand_RB + OnHand_S + OnHand_OEM+ OnOrder_RG + OnOrder_RB + OnOrder_S + OnOrder_OEM)) as balance
from cte_need a
inner join cte_open b
on a.po_month = b.del_month
and a.item = b.item
inner join cte_hand c
on a.po_month = c.oh_month
and a.item = c.item
)

select a.po_month, a.item,b.po_month as next_month, a.balance as current_month, b.balance as last_month, CASE WHEN (a.balance- b.balance) >= 0 THEN 0
ELSE (a.balance - b.balance) * -1
END AS balance
from cte_result a
left outer join cte_result b
on b.rno = a.rno-1
and a.item = b.item