SQLTeam.com | Weblogs | Forums

Rolling month balance carry over


#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]