USE [VEPROMS]
GO
/****** Object:  UserDefinedFunction [dbo].[vefn_CompareTranFormat]    Script Date: 02/26/2010 07:05:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* 
  Determines if two transition formats are same string.
	returns 0 if identical, 1 if similar (range for range, item for item), 2 if totally different
*/
ALTER FUNCTION [dbo].[vefn_CompareTranFormat] (@FormatID int, @NewFormatID int, @TranType int) RETURNS int
WITH EXECUTE AS OWNER
AS
BEGIN
	--
	IF @FormatID = @NewFormatID
		RETURN @TranType
	DECLARE @TransFormat varchar(max)
	DECLARE @NewTransFormat varchar(max)
	SET @TransFormat = isnull(.dbo.vefn_GetTransFormat(@FormatID, @TranType),'')
	SET @NewTransFormat = isnull(.dbo.vefn_GetTransFormat(@NewFormatID, @TranType),'')
	RETURN CASE 
		WHEN @TransFormat = @NewTransFormat THEN 0   -- transition formats are identical
		WHEN @TransFormat LIKE '%{Last Step}%' THEN
			CASE
				WHEN @NewTransFormat LIKE '%{Last Step}%' THEN 1  -- both ranges, but different format
				ELSE 2		-- totally different format, not even same type (was single, changed to range)
			END
		ELSE
			CASE
				WHEN @NewTransFormat LIKE '%{Last Step}%' THEN 2 -- totally different format, not even same type (was range, changed to single)
				ELSE 1	-- both single, but different format
			END
	END
END