CREATE PROCEDURE dbo.ProductPricePacketInfo 
(
	@AFromDate DATETIME, 
	@AEndDate DATETIME, 
	@AProductNumber INT 
)
/*

  Changes:
    MLJ 2022-10-06 Optimized
*/
AS
BEGIN
SET NOCOUNT ON

	DECLARE @Dates TABLE
	(
		IndividualDate datetime PRIMARY KEY
	)
	DECLARE @Date datetime = @AFromDate
	WHILE @Date <= @AEndDate
	BEGIN
		INSERT INTO @Dates VALUES (@Date)
		SET @Date = DATEADD(day, 1, @Date)
	END

	CREATE TABLE #PPP
	(
		Id int,
		StartDate datetime, 
		EndDate datetime, 
		PacketType int,
		FK_ProductNumber int
	)
	IF @AProductNumber=-9
		INSERT INTO #PPP
		SELECT PPP.Id, PPP.StartDate, PPP.EndDate, PPP.PacketType, PPP.FK_ProductNumber
		FROM ProductPricePacket PPP WITH (NOLOCK) 
		WHERE PPP.StartDate <= @AEndDate
		AND PPP.EndDate >= @AFromDate
	ELSE
		INSERT INTO #PPP
		SELECT PPP.Id, PPP.StartDate, PPP.EndDate, PPP.PacketType, PPP.FK_ProductNumber
		FROM ProductPricePacket PPP WITH (NOLOCK) 
		WHERE PPP.StartDate <= @AEndDate
		AND PPP.EndDate >= @AFromDate
		AND PPP.FK_ProductNumber=@AProductNumber

	SELECT dtRange.IndividualDate AS View_DateStamp, MAX(Id) AS View_Id, FK_ProductNumber AS View_FK_ProductNumber, PacketType AS View_PacketType
	FROM #PPP ProductPricePacket 
	INNER JOIN @Dates dtRange ON dtRange.IndividualDate BETWEEN ProductPricePacket.StartDate AND ProductPricePacket.EndDate
	GROUP BY dtRange.IndividualDate, FK_ProductNumber, PacketType

	DROP TABLE #PPP

END