http://dbshelp.devio.at/index.php?action=history&feed=atom&title=Dbo.ufnGetProductDealerPricefunction 2026-05-01T15:24:26Z Revision history for this page on the wiki MediaWiki 1.30.0 http://dbshelp.devio.at/index.php?title=Dbo.ufnGetProductDealerPricefunction&diff=2550&oldid=prev 2010-06-23T22:07:39Z

<p>New page: == wikibot == {| border=&quot;1&quot; cellpadding=&quot;5&quot; cellspacing=&quot;0&quot; style=&quot;border-collapse:collapse&quot; |- valign=&quot;top&quot; | &#039;&#039;&#039;Function | dbo.ufnGetProductDealerPrice |- valign=&quot;top&quot; | &#039;&#039;&#039;Description ...</p> <p><b>New page</b></p><div>== wikibot ==<br /> <br /> {| border=&quot;1&quot; cellpadding=&quot;5&quot; cellspacing=&quot;0&quot; style=&quot;border-collapse:collapse&quot;<br /> |- valign=&quot;top&quot;<br /> | '''Function<br /> | dbo.ufnGetProductDealerPrice<br /> |- valign=&quot;top&quot;<br /> | '''Description<br /> | Scalar function returning the dealer price for a given product on a particular order date.<br /> |}<br /> <br /> === Source ===<br /> &lt;pre&gt;<br /> CREATE FUNCTION [dbo].[ufnGetProductDealerPrice](@ProductID [int], @OrderDate [datetime])<br /> RETURNS [money] <br /> AS <br /> -- Returns the dealer price for the product on a specific date.<br /> BEGIN<br /> DECLARE @DealerPrice money;<br /> DECLARE @DealerDiscount money;<br /> <br /> SET @DealerDiscount = 0.60 -- 60% of list price<br /> <br /> SELECT @DealerPrice = plph.[ListPrice] * @DealerDiscount <br /> FROM [Production].[Product] p <br /> INNER JOIN [Production].[ProductListPriceHistory] plph <br /> ON p.[ProductID] = plph.[ProductID] <br /> AND p.[ProductID] = @ProductID <br /> AND @OrderDate BETWEEN plph.[StartDate] AND COALESCE(plph.[EndDate], CONVERT(datetime, '99991231', 112)); -- Make sure we get all the prices!<br /> <br /> RETURN @DealerPrice;<br /> END;<br /> &lt;/pre&gt;<br /> <br /> === References ===<br /> {| border=&quot;1&quot; cellpadding=&quot;5&quot; cellspacing=&quot;0&quot; style=&quot;border-collapse:collapse&quot;<br /> |- style=&quot;background:silver&quot;<br /> | '''Dependency Type<br /> | '''Object Type<br /> | '''Referenced Object<br /> <br /> |- valign=&quot;top&quot;<br /> | Select<br /> | Table<br /> | [[Production.Product_(table)|Production.Product]]<br /> <br /> |- valign=&quot;top&quot;<br /> | Select<br /> | Table<br /> | [[Production.ProductListPriceHistory_(table)|Production.ProductListPriceHistory]]<br /> <br /> |}</div>

Bot