dbo.ufnGetProductDealerPrice User Defined Function

Description

Scalar function returning the dealer price for a given product on a particular order date.

Properties

Creation Date	08/01/2010 08:41
Encrypted
Ansi Nulls

Parameters

Parameter	Direction	Description	Data Type	Size
@ProductID	In	Input parameter for the scalar function ufnGetProductDealerPrice. Enter a valid ProductID from the Production.Product table.	int	4
@OrderDate	In	Input parameter for the scalar function ufnGetProductDealerPrice. Enter a valid order date.	datetime	4
@RETURN_VALUE	Return Value		money	8

Objects that dbo.ufnGetProductDealerPrice depends on

Database Object	Object Type	Description	Dep Level
dbo.Flag	User Defined Data Type		2
dbo.Name	User Defined Data Type		2
Production.Product	Table	Products sold or used in the manfacturing of sold products.	1
Production.ProductCategory	Table	High-level product categorization.	3
Production.ProductListPriceHistory	Table	Changes in the list price of a product over time.	1
Production.ProductModel	Table	Product model classification.	2
Production.ProductSubcategory	Table	Product subcategories. See ProductCategory table.	2
Production.UnitMeasure	Table	Unit of measure lookup table.	2

Procedure Source Code

CREATE FUNCTION [dbo].[ufnGetProductDealerPrice](@ProductID [int], @OrderDate [datetime])
RETURNS [money] 
AS 

BEGIN
DECLARE @DealerPrice money;
DECLARE @DealerDiscount money;
SET @DealerDiscount = 0.60  -- 60% of list price
SELECT @DealerPrice = plph.[ListPrice] * @DealerDiscount 
FROM [Production].[Product] p 
INNER JOIN [Production].[ProductListPriceHistory] plph 
ON p.[ProductID] = plph.[ProductID] 
AND p.[ProductID] = @ProductID 
AND @OrderDate BETWEEN plph.[StartDate] AND COALESCE(plph.[EndDate], CONVERT(datetime, '99991231', 112)); -- Make sure we get all the prices!
RETURN @DealerPrice;
END;

Related Objects

dbo Schema
AdventureWorks Database