Using Temporary Variables within Table Scalar Functions


Introduction

Due to recent changes in the business model of the organisation I work for, most planning is now done “off-system”, and the people who conduct the planning do it via Excel spread sheets. As such I have been required to export data to them in a pre-defined format. My original SSRS report had most of the query internally, however after a few weeks, it was clear they wanted the data to be shown in multiple locations, and the original report was not efficient. Taking up to 4 minutes 45 seconds to run.

The key issue was the way I was using an in house function called “PBTI_IH_GetBusinessDays”. The function counts the number of week days between 2 dates, subtracting any days we have listed as holiday days in the PBTI manufacturing calendar. You may or may not be aware of the, but using a function within a query turns it from a “set” based query to a “row” based query. I often use the function so set a variable prior to running the query, then use the variable within the query body to overcome this, however due to the way functions work, I could not set the variable in the normal way. Listed below is the code I eventually created after quite a bit of digging online!

The Report

Here is a quick snapshot of the SSRS report generated from this function – I’ve had to distort the data prior to taking the snapshot for confidentiality reasons, but it gives a clear indication of the overall goal.

SSRS Reach Report

SSRS Reach Report

The Code

ALTER function PBTI_IH_FN_TurbonReach (
 @startdate date = ‘2014-08-01’
 ,@enddate date = ‘2014-10-31’
)

/*declare @busdays as int = dbo.pbti_ih_getbusinessdays(@startdate,@enddate)
 , cte.AvgMonthBusDays as int = dbo.pbti_ih_getbusinessdays(DATEADD(yy, DATEDIFF(yy,0,getdate()), 0),  DATEADD(yy, DATEDIFF(yy,0,getdate()) + 1, -1))/12*/
returns @table table
  (
   item dbo.itemtype
   ,TMCCode nvarchar(35)
   ,TEGCode nvarchar(35)
   ,description dbo.descriptiontype
   ,qty_on_hand dbo.amounttype
   ,SalesOrdersOnHand dbo.amounttype
   ,OpenPurchaseOrders dbo.amounttype
   ,SalesOrdersMonth0 dbo.amounttype
   ,SalesOrdersMonth1 dbo.amounttype
   ,SalesOrdersMonth2 dbo.amounttype
   ,SalesOrdersMonth3 dbo.amounttype
   ,AverageDailySales dbo.amounttype
   ,AverageMonthlySales dbo.amounttype
   ,HistoricSalesMonth1 dbo.amounttype
   ,HistoricSalesMonth2 dbo.amounttype
   ,HistoricSalesMonth3 dbo.amounttype
   ,ReachMonth dbo.amounttype
   ,Missing30Days dbo.amounttype
   ,Missing60Days dbo.amounttype
   ,itemtype nvarchar(1)
  )
as
begin
declare @busdays decimal(10,2) = dbo.pbti_ih_getbusinessdays(@startdate,@enddate)
  ,@AvgMonthBusDays decimal(10,2) = dbo.pbti_ih_getbusinessdays(DATEADD(yy, DATEDIFF(yy,0,getdate()), 0),  DATEADD(yy, DATEDIFF(yy,0,getdate()) + 1, -1))/12

insert @table
select
 i.item
 , isnull(i.uf_tcode,’Unknown’) as TMCCode
 , isnull(i.uf_tegcode,’Unknown’) as TEGCode
 , i.description
 , qoh.qty_on_hand
 , isnull(oobm.totaloutstanding,0) as SalesOrdersOnHand
 , isnull(poos.OnOrder,0) as OpenPurchaseOrders
 , isnull(oobm.month0,0)as SalesOrdersMonth0
 , isnull(oobm.month1,0)as SalesOrdersMonth1
 , isnull(oobm.month2,0)as SalesOrdersMonth2
 , isnull(oobm.month3,0)as SalesOrdersMonth3
 , isnull(ads.averagedailysaleswithinperiod,0) AverageDailySales
 , isnull(ads.averagedailysaleswithinperiod,0)*  @AvgMonthBusDays AverageMonthlySales
 , isnull(hs.M1,0) as HistoricSalesMonth1
 , isnull(hs.M2,0) as HistoricSalesMonth2
 , isnull(hs.M3,0) as HistoricSalesMonth3
 , case
  when qoh.qty_on_hand is null then 0
  when ads.AverageDailySalesWithinPeriod is null then 0
  when ads.AverageDailySalesWithinPeriod = 0 then 0
  else isnull((qoh.qty_on_hand + isnull(poos.onorder,0)) / (ads.AverageDailySalesWithinPeriod * 21),0)
 end ReachMonth
 , case
   when(isnull(qoh.qty_on_hand,0)+isnull(poos.onorder,0) – (isnull(ads.AverageDailySalesWithinPeriod,0) * @AvgMonthBusDays))*-1
   < 1 then 0
   else (isnull(qoh.qty_on_hand,0)+isnull(poos.onorder,0) – (isnull(ads.AverageDailySalesWithinPeriod,0) * @AvgMonthBusDays))*-1
   end
   as Missing30Days
 , case
   when(isnull(qoh.qty_on_hand,0)+isnull(poos.onorder,0) – (isnull(ads.AverageDailySalesWithinPeriod,0) * (@AvgMonthBusDays * 2)))*-1
   < 1 then 0
   else (isnull(qoh.qty_on_hand,0)+isnull(poos.onorder,0) – (isnull(ads.AverageDailySalesWithinPeriod,0) * (@AvgMonthBusDays * 2)))*-1
   end
   as Missing60Days
 , it.itemtype
from item as i
–cross apply cte
left join PBTI_IH_VW_OpenOrdersByMonth as oobm on oobm.item = i.item
left join PBTI_IH_FN_HistoricSales(@startdate,@enddate) as hs /*Historic Sales */ on hs.item = i.item
left join PBTI_IH_VW_QtyOnHand as qoh on qoh.item = i.item
left join PBTI_IH_VW_OpenPurchaseOrdersSummary as poos /*purchase orders outstanding */ on poos.item = i.item
left join PBTI_IH_FN_AverageDailySales(@startdate, @enddate, @busdays) as ads on ads.item = i.item /*Why can I not take out the CTE and why are we passing in qoh? */
left join pbti_ih_vw_itemtype as it on i.plan_code = it.plan_code

where i.stat <> ‘o’
return
end

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.