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]