compare.sql

-- UDF definition:
CREATE FUNCTION [dbo].[GetSessionParameter]
(
	@key nvarchar(255),
	@defaultValue nvarchar(MAX) = null
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
	DECLARE @Ret as NVARCHAR(MAX)

	SELECT
		@Ret = [Value]
	FROM
		[UserSessionParameters]
	WHERE
		[Key] = @key

	IF @@ROWCOUNT = 0
	BEGIN
		SET @ret = @defaultValue;
	END

	RETURN @ret;
END



-- version 1
SELECT
	PartNumber,
	AnnualUsage,
	[AnnualUsage] * (
		DATEDIFF(
			day,
			CONVERT(datetime, dbo.GetSessionParameter('Purchasing.BeginningDate', GETDATE())),
			CONVERT(datetime, dbo.GetSessionParameter('Purchasing.EndingDate', GETDATE()))
		) + 1) / 365.0 AS [ProjectedConsumption]
FROM
	[Purchas_Invt_Parts-1]
	
-- version 2
SELECT
	PartNumber,
	AnnualUsage,
	[AnnualUsage] * (
		DATEDIFF(
			day,
			CONVERT(datetime, [SP].BeginningDate),
			CONVERT(datetime, [SP].EndingDate)
		) + 1) / 365.0 AS [ProjectedConsumption]
FROM
	[Purchas_Invt_Parts-1]
	LEFT JOIN
		(SELECT
			dbo.GetSessionParameter('Purchasing.BeginningDate', GETDATE()) AS [BeginningDate],
			dbo.GetSessionParameter('Purchasing.EndingDate', GETDATE()) AS [EndingDate]
		) AS [SP]
		ON 1 = 1