If Exists (Select * From dbo.sysobjects where id = object_id(N'[dbo].[qryMMS_Forecasting_TransactionItemIntervalAggregate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[qryMMS_Forecasting_TransactionItemIntervalAggregate]
GO
CREATE PROCEDURE [dbo].[qryMMS_Forecasting_TransactionItemIntervalAggregate]
AS
Begin


declare @highWatermark bigint
declare @newHighWatermark bigint
declare @loId bigint
declare @hiId bigint
declare @rowCount int = 1 
declare @step bigint = 10000000
declare @businessDay dateTime

-- Grap current and new highwatermark.
Select @highWatermark = IntervalHighWatermark 
	from forecasting.TableHighWatermark 
	Where TableName = 'tbTransactionItem'

Set @loId = @highWatermark

	
	-- get businessdays for last 7 days and 2 days in the the future 
		DECLARE @start_date DATE = DateAdd(d,-7,GETDATE());
		DECLARE @end_date DATE =  DateAdd(d,2,GETDATE());
		DECLARE @tempBusinessDayList TABLE
				(BusinessDay DATE)

		;WITH    BusinessDays
				  AS ( SELECT   @start_date AS [Date]
					   UNION ALL
					   SELECT   DATEADD(DAY, 1, [Date])
					   FROM     BusinessDays
					   WHERE    [Date] < @end_date )

			Insert into @tempBusinessDayList
								SELECT [Date] FROM BusinessDays OPTION (MAXRECURSION 0)
								
								
								
	  DECLARE @maxTransactionItemId BIGINT = 0; 
      DECLARE @businessDate SMALLDATETIME 
      DECLARE businessday_cursor CURSOR fast_forward FOR 
        SELECT businessday 
        FROM   @tempBusinessDayList 

      OPEN businessday_cursor 

		  FETCH next FROM businessday_cursor INTO @businessDate 

			  WHILE @@FETCH_STATUS = 0 
				BEGIN 
					DECLARE @tempId BIGINT = 0; 

					SELECT @tempId = Isnull(Max(transactionitemid), 0) 
					FROM   tbtransactionitem WITH (nolock) 
					WHERE  businessday = @businessDate 

					IF ( @tempId > @maxTransactionItemId ) 
					  BEGIN 
						  SET @maxTransactionItemId = @tempId 
					  END 

					FETCH next FROM businessday_cursor INTO @businessDate 
				END 

      CLOSE businessday_cursor 

      DEALLOCATE businessday_cursor 

      SET @newHighWatermark = @maxTransactionItemId 

if(@highWatermark = @newHighwatermark) return


set @hiId = CASE WHEN (@loId + @step) < @newHighWatermark Then (@loId + @step) Else @newHighWatermark End


While 1=1
begin
	Begin Tran

	Insert into forecasting.InventoryItemIntervalAggregate 
		(EntityId, 
		BusinessDay,
		ItemId,
		IntervalStart,
		ServiceType,
		Quantity)
	Select ti.EntityId, 
			ti.BusinessDay, 
			ti.ItemId,
			convert(smalldatetime,Floor(cast(ti.TransactionTime as float) * 96)/96) AS IntervalStart, 
			ts.InOutDel AS ServiceType,
			SUM(ti.Quantity) AS Quantity
		FROM tbTransactionItem ti with (nolock) 
			JOIN tbTransactionSalesItem ts with (nolock forceseek)
				on ti.TransactionSalesItemID = ts.TransactionSalesItemID
		Where TransactionItemID > @loId
			And TransactionItemID <= @hiId
			And ti.BusinessDay in (select BusinessDay from @tempBusinessDayList)
			AND ti.TransactionTime < '6/6/2079' --Prevent overflow of smalldatetime
		Group By ti.EntityId, 
				ti.ItemID, 
				ti.BusinessDay, 
				convert(smalldatetime,Floor(cast(ti.TransactionTime as float) * 96)/96), 
				ts.InOutDel


	Update Forecasting.TableHighWatermark
	Set IntervalHighWatermark = @hiId
	Where TableName = 'tbTransactionItem'


	Commit Tran

	if(@hiId = @newHighWatermark) return	

	set @loId = @hiId
	set @hiId = CASE WHEN (@loId + @step) < @newHighWatermark Then (@loId + @step) Else @newHighWatermark End
End

End