dbo.ufnGetProductDealerPrice User Defined Function

In This Topic

Description

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

Properties

Creation Date	27/10/2017 14:33
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.	Integer	4
@OrderDate	In	Input parameter for the scalar function ufnGetProductDealerPrice. Enter a valid order date.	DBTimeStamp	4
@RETURN_VALUE	Return Value		Currency	8

Objects that dbo.ufnGetProductDealerPrice depends on

Database Object	Object Type	Description	Dep Level
dbo.Flag	User Defined Data Type		4
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.	2
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 

-- Returns the dealer price for the product on a specific date.
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

Send comments on this topic.