dbo.ufnGetProductStandardCost
Documentation | AdventureWorks |
Schema | dbo |
Name | ufnGetProductStandardCost |
Module | Products |
Scalar function returning the standard cost for a given product on a particular order date.
Input/Output
Mode | Name | Data type | Description | |
---|---|---|---|---|
1 | OUT | Returns | money | |
2 | IN | ProductID | int | Input parameter for the scalar function ufnGetProductStandardCost. Enter a valid ProductID from the Production.Product table. |
Input parameter for the scalar function ufnGetProductStandardCost. Enter a valid ProductID from the Production.Product table. | ||||
3 | IN | OrderDate | datetime | Input parameter for the scalar function ufnGetProductStandardCost. Enter a valid order date. |
Input parameter for the scalar function ufnGetProductStandardCost. Enter a valid order date. |
Uses
Name |
---|
dbo.ufnGetProductStandardCost
|
Script
CREATE FUNCTION [ufnGetProductStandardCost](@ProductID [int], @OrderDate [datetime]) RETURNS [money] AS -- Returns the standard cost for the product on a specific date. BEGIN DECLARE @StandardCost money; SELECT @StandardCost = pch.[StandardCost] FROM [Production].[Product] p INNER JOIN [Production].[ProductCostHistory] pch ON p.[ProductID] = pch.[ProductID] AND p.[ProductID] = @ProductID AND @OrderDate BETWEEN pch.[StartDate] AND COALESCE(pch.[EndDate], CONVERT(datetime, '99991231', 112)); -- Make sure we get all the prices! RETURN @StandardCost; END; |
Exported: 2019-02-04 23:13, Last imported: 2018-03-07 11:56