--This file accompanies the web article entitled: The Problem with User-Defined Functions and a Solution.
--It is intended for the educational purposes only. Novick Software does not warrant this software in any way 
-- for any purpose other then illustrating the article. 
--You can read the article at : http://www.novicksoftware.com/problem-user-defined-functions-solution/
--There is also a video you can find it on youtube
-- Copyright 2014 - Novick Software, Inc.

-- This preliminary script creates two tables in tempdb by copying rows from the 
-- Adventureworks2014 sample database from Codeplex.
use tempdb
-- Set up the demo by copying data from 
if OBJECT_ID('security_returns') is not null drop table security_returns
select SalesOrderID*1000+SalesOrderDetailID security_id, LineTotal / ProductID return_ema into security_returns 
	from AdventureWorks2014.Sales.SalesOrderDetail
alter table security_returns alter column security_id int not null
alter table security_returns add constraint pk_security_returns primary key clustered (security_id)
if OBJECT_ID('todays_returns') is not null drop table todays_returns
select security_id, return_ema * .95 returns_today into todays_returns from security_returns
alter table todays_returns alter column security_id int not null
GO
-- Turn on advanced options 
EXEC sp_configure 'show advanced options', 1
reconfigure
-- Change the cost threshold for parallelism to ensure our demo shows the parallel difference
EXEC sp_configure 'cost threshold for parallelism', 0;
RECONFIGURE

--- >>>>> There is a cleanup script at the bottom to return the cost threashold to 5  <<<<<<
--- >>>>> There is a cleanup script at the bottom to return the cost threashold to 5  <<<<<<

go -- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
if OBJECT_ID('[dbo].[exp_moving_avg_inline]') is not null 
      drop function [dbo].[exp_moving_avg_scalar]
set statistics time off
set statistics IO off
go -- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>




CREATE FUNCTION dbo.exp_moving_avg_scalar (

	@ema_yesterday FLOAT
  , @value_today FLOAT
  , @days INT
  , @smoothing FLOAT = 2.0
 
 ) RETURNS FLOAT /* Single value exponential moving average (EMA)
*
* Example:
select dbo.exp_moving_avg_scalar(3.545, 2.123, 21, default)
*
* When
* 2013-10-11 Andy Novick         Initial Coding
***********************************************************************/
AS BEGIN
 
 RETURN CASE WHEN @ema_yesterday IS NULL 
			 THEN @value_today
			 ELSE (@value_today * (@smoothing/(1.0+@days)))	
			       + (@ema_yesterday * (1.0 - (@smoothing/(1.0+@days)))) 
			 END      
END
go -- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>









if OBJECT_ID('tempdb..#example_table') is not null drop table #example_table
set statistics time ON
go -- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>



SELECT COALESCE (yesterday.security_id, today.security_id) security_id
    ,  yesterday.return_ema returns_ema_yesterday
	,  dbo.exp_moving_avg_scalar(yesterday.return_ema
	                            ,today.returns_today
						   	    ,21  -- @days
							 	,default -- @smoothing
							) returns_ema_today
		into #example_table
	FROM security_returns yesterday 
	FULL OUTER JOIN todays_returns today
		on yesterday.security_id = today.security_id
go -- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>









SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
if OBJECT_ID('[dbo].[exp_moving_avg_inline]') is not null 
      drop function [dbo].[exp_moving_avg_inline]
GO -- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>





CREATE FUNCTION [dbo].[exp_moving_avg_inline] (

	@ema_yesterday FLOAT
  , @value_today FLOAT
  , @days INT
  , @smoothing FLOAT = 2.0
 
 ) RETURNS TABLE /* Single value exponential moving average (EMA)
*
* Example:
select ema_today from dbo.exp_moving_avg_inline(3.545, 2.123, 21, default)
*
* When
* 2013-10-11 Andy Novick         Initial Coding
***********************************************************************/
AS RETURN 
 SELECT CASE WHEN @ema_yesterday IS NULL 
			 THEN @value_today
			 ELSE (@value_today * (@smoothing/(1.0+@days)))	
			       + (@ema_yesterday * (1.0 - (@smoothing/(1.0+@days)))
				  ) 
			 END      
	   as ema_today 


go -- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>


set statistics time OFF
if OBJECT_ID('tempdb..#example_table2') is not null drop table #example_table2
set statistics time ON
go -- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>






-- use the inline version of exponential moving value in a query

SELECT COALESCE (yesterday.security_id, today.security_id) security_id
    ,  yesterday.return_ema returns_ema_yesterday
	,  (SELECT ema_today FROM dbo.exp_moving_avg_inline(yesterday.return_ema
	                                ,today.returns_today
									,21  -- @days
									,default -- @smoothing
									) 
       ) returns_ema_today
	   into #example_table2
	FROM security_returns yesterday 
	FULL OUTER JOIN todays_returns today
		on yesterday.security_id = today.security_id

go -- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>













--- Return the cost threshold for parallelism to it's normal value
EXEC sp_configure 'cost threshold for parallelism', 0;
RECONFIGURE
-- And turn off advanced options
EXEC sp_configure 'show advanced options', 1
reconfigure
go -- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>