| Latest revision |
Your text |
| Line 1: |
Line 1: |
| | == wikibot == | | == wikibot == |
| − |
| |
| − | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
| |
| − | |- valign="top"
| |
| − | | '''Function
| |
| − | | dbo.ufnGetProductDealerPrice
| |
| − | |- valign="top"
| |
| − | | '''Description
| |
| − | | Scalar function returning the dealer price for a given product on a particular order date.
| |
| − | |}
| |
| − |
| |
| − | === Source ===
| |
| − | <pre>
| |
| − | 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;
| |
| − | </pre>
| |
| − |
| |
| − | === References ===
| |
| − | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
| |
| − | |- style="background:silver"
| |
| − | | '''Dependency Type
| |
| − | | '''Object Type
| |
| − | | '''Referenced Object
| |
| − |
| |
| − | |- valign="top"
| |
| − | | Select
| |
| − | | Table
| |
| − | | [[Production.Product_(table)|Production.Product]]
| |
| − |
| |
| − | |- valign="top"
| |
| − | | Select
| |
| − | | Table
| |
| − | | [[Production.ProductListPriceHistory_(table)|Production.ProductListPriceHistory]]
| |
| − |
| |
| − | |}
| |
| − |
| |
| − |
| |
| − | == automatically generated ==
| |
| | | | |
| | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |
| Line 59: |
Line 5: |
| | | '''function | | | '''function |
| | | dbo.ufnGetProductDealerPrice | | | dbo.ufnGetProductDealerPrice |
| − | |- valign="top"
| |
| − | | '''Description
| |
| − | | Scalar function returning the dealer price for a given product on a particular order date.
| |
| | |- | | |- |
| | |} | | |} |
| | | | |
| | <pre> | | <pre> |
| − |
| |
| | CREATE FUNCTION [dbo].[ufnGetProductDealerPrice](@ProductID [int], @OrderDate [datetime]) | | CREATE FUNCTION [dbo].[ufnGetProductDealerPrice](@ProductID [int], @OrderDate [datetime]) |
| | RETURNS [money] | | RETURNS [money] |
| Line 87: |
Line 29: |
| | END; | | END; |
| | </pre> | | </pre> |
| − |
| |
| − |
| |
| − | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
| |
| − | |- style="background:silver"
| |
| − | | '''Dependency Type
| |
| − | | '''Object Type
| |
| − | | '''Referenced Object
| |
| − |
| |
| − | |-
| |
| − | | Select
| |
| − | | Table
| |
| − | | [[Production.Product_(table)|Production.Product]]
| |
| − |
| |
| − | |-
| |
| − | | Select
| |
| − | | Table
| |
| − | | [[Production.ProductListPriceHistory_(table)|Production.ProductListPriceHistory]]
| |
| − |
| |
| − | |}
| |
