SQLTeam.com | Weblogs | Forums

Rolling balance to carry to next month

sql2008r2
#1

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

#2

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

#3

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

#4

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]

#5

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

#6

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.

#7

;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