ALTER PROCEDURE [dbo].[GetProductPricesForDate] 
-- Add the parameters for the stored procedure here
@Date datetime, 
@ProductId uniqueidentifier
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT 
rpph.RawProductPromotionalHistoryLocationId, 
rpph.AisleNumber, 
rpph.RowLetter, 
rpph.PromotionalLocationStartDate, 
rpph.PromotionalLocationEndDate, 
rpph.PromotionalLocationCategoryId, 
rpph.PromotionalLocationName, 
rpph.PromotionalLocationIdentifier, 
ph.[DateTime], p.Name AS 'ProductName', ph.UnitPrice, rp.RawProductId, r.LogoFileName AS 'ShopLogoFileName', r.ShopId, r.Name as 'ShopName', 
rpph.PromotionalText, rpph.RawProductPromotionalHistoryId, rpph.StartDateTime, rpph.EndDateTime, rpph.PromotionalCategoryId, 
rpph.Name AS 'PromotionalCategoryName', rpph.Identifier AS 'PromotionalIdentifier', rpph.DepthOfCutPrice, rpph.DepthOfCutPercentage
FROM Shops r
INNER JOIN RawProducts rp
ON rp.[ShopId]=r.ShopId
INNER JOIN Products p
ON p.[ProductId] = rp.ProductId
INNER JOIN RawProductPriceHistory ph
ON ph.[RawProductId] = rp.RawProductId
INNER JOIN 
(SELECT RawProductId,MAX([DateTime]) as maxdate
FROM RawProductPriceHistory 
WHERE [DateTime]<=@Date
GROUP BY RawProductId
)temp
ON ph.RawProductId=temp.RawProductId
AND ph.[DateTime]=temp.maxdate
LEFT JOIN 
( SELECT promohis.*, 
  promocat.Name, 
  promocat.Identifier, 
  rpphl.RawProductPromotionalHistoryLocationId, 
  rpphl.AisleNumber, 
  rpphl.RowLetter, 
  rpphl.StartDate AS 'PromotionalLocationStartDate', 
  rpphl.EndDate AS 'PromotionalLocationEndDate', 
  rpphl.PromotionalLocationCategoryId, 
  rpphl.Name AS 'PromotionalLocationName', 
  rpphl.Identifier AS 'PromotionalLocationIdentifier'
  FROM RawProductPromotionalHistory promohis, 
  PromotionalCategories promocat 
  LEFT JOIN 
  (  SELECT 
     promohislocs.RawProductPromotionalHistoryLocationId, 
     promohislocs.RawProductPromotionalHistoryId, 
     promohislocs.AisleNumber, 
     promohislocs.RowLetter, 
     promohislocs.StartDate, 
     promohislocs.EndDate,
     promoloccat.PromotionalLocationCategoryId, 
     promoloccat.Name, 
     promoloccat.Identifier
     FROM RawProductPromotionalHistoryLocations promohislocs,
     PromotionalLocationCategories promoloccat
     WHERE promohislocs.PromotionalLocationCategoryId = 
     promoloccat.PromotionalLocationCategoryId
     AND promohislocs.StartDate<=@Date 
     AND (promohislocs.EndDate>=@Date 
     OR promohislocs.EndDate IS NULL)
   )rpphl
     ON rpphl.RawProductPromotionalHistoryId = 
     promohis.RawProductPromotionalHistoryId
     WHERE promohis.PromotionalCategoryId = 
     promocat.PromotionalCategoryId 
     AND StartDateTime<=@Date 
     AND (EndDateTime>=@Date OR EndDateTime IS NULL)
)rpph
ON rpph.RawProductId=rp.RawProductId
WHERE p.ProductId = @ProductId 
GROUP BY 
rpph.RawProductPromotionalHistoryLocationId, 
rpph.AisleNumber, 
rpph.RowLetter, 
rpph.PromotionalLocationStartDate, 
rpph.PromotionalLocationEndDate, 
rpph.PromotionalLocationCategoryId, 
rpph.PromotionalLocationName, 
rpph.PromotionalLocationIdentifier, 
ph.[DateTime], p.Name, r.Name, ph.UnitPrice, r.ShopId, rp.RawProductId, r.LogoFileName, 
rpph.PromotionalText, rpph.RawProductPromotionalHistoryId, rpph.StartDateTime, rpph.EndDateTime, rpph.PromotionalCategoryId, 
rpph.Name, rpph.Identifier, rpph.DepthOfCutPrice, rpph.DepthOfCutPercentage
ORDER BY ph.UnitPrice DESC

END