Function: dbo.ufnGetProductDealerPrice

Properties

Owner:	dbo
Type:	SQL scalar function
Encrypted:
Creation Date:	04/26/2006
Modification Date:	04/26/2006
Description:	Scalar function returning the dealer price for a given product on a particular order date.

Creation options

QUOTED_IDENTIFIER:
ANSI_NULLS:

Parameters

Name	Direction	DataType	Length	Default	Description
@RETURN_VALUE	RETURN	money	8
@ProductID	INPUT	int	4		Input parameter for the scalar function ufnGetProductDealerPrice. Enter a valid ProductID from the Production.Product table.
@OrderDate	INPUT	datetime	8		Input parameter for the scalar function ufnGetProductDealerPrice. Enter a valid order date.

Total: 3 parameter(s)

Objects that [dbo].[ufnGetProductDealerPrice] depends on

Object Name	Owner	Object Type	Dep Level
Flag	dbo	User Defined type	1
Name	dbo	User Defined type	1
ProductCategory	Production	Table	2
ProductModel	Production	Table	2
UnitMeasure	Production	Table	2
ProductSubcategory	Production	Table	3
Product	Production	Table	4
ProductListPriceHistory	Production	Table	5

Total: 8 object(s)

SQL

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

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;

GO
SET QUOTED_IDENTIFIER OFF
GO

GO
SET ANSI_NULLS OFF
GO