I'm studing the Ms SQL AdventureWorks 2014, to model an internal dbase for our company
I usually work on Postgres, and I'm trying to "understand" Ms SQL stored procedures... :-)) BUT ....
The store Procedure [dbo.ufnGetProductListPrice ] SEEMS STRANGE to me.
the SQL code is found here:
(https://dataedo.com/samples/html/AdventureWorks/doc/AdventureWorks_2/functions/dbo_ufnGetProductListPrice_116.html)
CREATE FUNCTION [dbo].[ufnGetProductListPrice](@ProductID [int], @OrderDate [datetime])
RETURNS [money]
AS
BEGIN
DECLARE @ListPrice money;
SELECT @ListPrice = plph.[ListPrice]
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 @ListPrice;
END;
The function is making use of the following two tables:
[Production.Product] (https://dataedo.com/samples/html/AdventureWorks/doc/AdventureWorks_2/tables/Production_Product_153.html)
[Production.ProductListPriceHistory] - (https://dataedo.com/samples/html/AdventureWorks/doc/AdventureWorks_2/tables/Production_ProductListPriceHistory_159.html)
In particular my doubts are:
the function is getting the parameter @ProductID.
The same @ProductID is used as the primary key of the Production.Product AND as part of the primary key for the table Production.ProductListPriceHistory
so is seems of no help making a join on Product.[ProductID] = ProductListPriceHistory.[ProductID]
when we can test the ProductID directly on the ProductListPriceHistory.[ProductID]
Why to create such a join ? Seems of no help...
The given @Orderdate datetime received as second parameter, is checked in the JOIN against the following condition
AND @OrderDate BETWEEN plph.[StartDate] AND COALESCE(plph.[EndDate], CONVERT(datetime, '99991231', 112)); -- Make sure we get all the prices!
BUT, if we are calling the store procedure for @Orderdate = 1/1/2022,
considering that [EndDate] could be NULL,
and we could have in ProductListPriceHistory.StartDate two records with our @ProductID, the first with StartDate=1/1/2020 and the second StartDate=1/1/2021,
such "BETWEEN" condition should match both of them, when obviously we would expect the last one ....
is it a bug ?
