Difference between revisions of "Dbo.ufnGetProductStandardCost (function)"

From dbscript Online Help
Jump to: navigation, search
Line 2: Line 2:
  
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|-
+
|- valign="top"
 
| '''Function
 
| '''Function
 
| dbo.ufnGetProductStandardCost
 
| dbo.ufnGetProductStandardCost
Line 8: Line 8:
 
| '''Description
 
| '''Description
 
| Scalar function returning the standard cost for a given product on a particular order date.
 
| Scalar function returning the standard cost for a given product on a particular order date.
|-
 
 
|}
 
|}
 
  
 
=== Source ===
 
=== Source ===
Line 39: Line 37:
 
| '''Referenced Object
 
| '''Referenced Object
  
|-
+
|- valign="top"
 
| Select
 
| Select
 
| Table
 
| Table
| [[Production.Product_(table)|Production.Product]]
+
| [[Production.Product_(_(table))|Production.Product]]
  
|-
+
|- valign="top"
 
| Select
 
| Select
 
| Table
 
| Table
| [[Production.ProductCostHistory_(table)|Production.ProductCostHistory]]
+
| [[Production.ProductCostHistory_(_(table))|Production.ProductCostHistory]]
  
 
|}
 
|}

Revision as of 22:59, 23 June 2010

wikibot

Function dbo.ufnGetProductStandardCost
Description Scalar function returning the standard cost for a given product on a particular order date.

Source

CREATE FUNCTION [dbo].[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;

References

Dependency Type Object Type Referenced Object
Select Table Production.Product
Select Table Production.ProductCostHistory


automatically generated

function dbo.ufnGetProductStandardCost
Description Scalar function returning the standard cost for a given product on a particular order date.

CREATE FUNCTION [dbo].[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;


Dependency Type Object Type Referenced Object
Select Table Production.Product
Select Table Production.ProductCostHistory