declare @EntityId bigint = 416
--mxls 00713
declare @Month datetime = '2019-01-01'
declare @lastCount datetime
declare @recentCount datetime
declare @noofmonths int
declare @previousStockCountLocationId bigint
SELECT top 1 @lastCount =  DATEADD(month, 1, ApplicablePeriod) , @previousStockCountLocationId = TransactionStockCountLocationID
FROM   tbTransactionStockCountLocation 
WHERE  EntityId = @EntityId 
       and StockCountTypeID = 4 
       and StockCountSubTypeID is null
       and Status = 'Applied' 
ORDER  BY ApplyDate desc
--mxls 1171
set @recentCount = @Month

DECLARE @i int
DECLARE @date_id datetime
DECLARE @date_table TABLE (
    idx smallint Primary Key IDENTITY(1,1)
    , date_id datetime
)


;WITH theDates AS
     (SELECT @lastCount theDate
      UNION ALL
      SELECT DATEADD(month, 1, theDate)
        FROM theDates
       WHERE DATEADD(month, 1, theDate) <= @recentCount
     )
 insert into @date_table (date_id)
SELECT theDate   FROM theDates


declare  @numrows int
SET @i = 1
SET @numrows = (SELECT COUNT(*) FROM @date_table)
if @numrows > 0
  WHILE (@i <= (SELECT MAX(idx) FROM @date_table))
    BEGIN
    
    SET @date_id = (SELECT date_id FROM @date_table WHERE idx = @i)
    

 declare @TransactionStockCountLocationId bigint   
INSERT INTO tbTransactionStockCountLocation
           ([EntityID]
           ,[ApplyDate]
           ,[ClerkID]
           ,[ClerkName]
           ,[CreateDate]
           ,[StockCountType]
           ,[StockCountName]
           ,[Status]
           ,[StockCountTypeID]
           ,[ApplicablePeriod]
           ,[AutoGenerated]
           ,[ApplyStatus] )
     VALUES
           (@entityId
           ,DateAdd(day,15,@date_id)
           ,1
           ,'systemadmin'
           ,GETDATE()
           ,'Monthly'
           , CONCAT('Monthly Count - ' , @date_id)
           ,'Applied'
           ,4
           ,@date_id
           ,0
           ,2 )

SET @TransactionStockCountLocationId = SCOPE_IDENTITY()
declare @itemid bigint
  select top 1 @itemid = ItemID  from tbTransactionStockCountLocationDetail
  where TransactionStockCountLocationID = @previousStockCountLocationId and Apply = 1

insert into tbTransactionStockCountLocationDetail
           ([TransactionStockCountLocationID]
           ,[LocationID]
           ,[ItemID]
           ,[StockCount]
           ,[StockOnHand1]
           ,[StockOnHand2]
           ,[StockOnHand3]
           ,[StockUnit1]
           ,[StockUnit2]
           ,[StockUnit3]
           ,[Apply]
           ,[ReadyToApply]
           ,[CreateDate]
           ,[CountTime]
           ,[HasVariance]
           ,[StockOnHand4]
           ,[StockUnit4]
           ,[VariancePercent])
    select top 1 
    @TransactionStockCountLocationId,
    LocationID,
    ItemID,
   StockCount,
   StockOnHand1,
   StockOnHand2,
   StockOnHand3,
   StockUnit1,
   StockUnit2,
   StockUnit3,
   Apply,
   ReadyToApply,
   CreateDate,
   CountTime,
   HasVariance,
   StockOnHand4,
   StockUnit4,
   VariancePercent    
 from tbTransactionStockCountLocationDetail where TransactionStockCountLocationID = @previousStockCountLocationId and Apply = 1

INSERT INTO tbTransactionStockCount
           ([EntityID]
           ,[FiscalYear]
           ,[StartDate]
           ,[EndDate]
           ,[PeriodNo]
           ,[ItemID]
           ,[StockCount]
           ,[Adjust_amt]
           ,[Adjust_qty]
           ,[Inventory_amt]
           ,[Inventory_qty]
           ,[StockCountDate]
           ,[ApplyDate]
           ,[ClerkID]
           ,[ClerkName]
           ,[CreateDate]
           ,[StockOnHand1]
           ,[StockOnHand2]
           ,[StockOnHand3]
           ,[StockUnit1]
           ,[StockUnit2]
           ,[StockUnit3]
           ,[TransactionStockCountLocationID]
           ,[BusinessDay]
           ,[ApplyAdjust_qty]
           ,[ApplyAdjust_amt]
           ,[CountTimeQty]
           ,[UnitCost]
           ,[Modified]
           ,[OriginalApplyDate]
           ,[StockOnHand4]
           ,[StockUnit4]
           ,[OriginalBusinessDay]
           ,[OnHandQuantity])     
     SELECT top 1
           EntityID
           ,FiscalYear
           ,StartDate
           ,EndDate
           ,PeriodNo
           ,ItemID
           ,StockCount
           ,Adjust_amt
           ,Adjust_qty
           ,Inventory_amt
           ,Inventory_qty
           ,StockCountDate
           ,ApplyDate
           ,ClerkID
           ,ClerkName
           ,CreateDate
           ,StockOnHand1
           ,StockOnHand2
           ,StockOnHand3
           ,StockUnit1
           ,StockUnit2
           ,StockUnit3
           ,@TransactionStockCountLocationId
           ,BusinessDay
           ,ApplyAdjust_qty
           ,ApplyAdjust_amt
           ,CountTimeQty
           ,UnitCost
           ,Modified
           ,OriginalApplyDate
           ,StockOnHand4
           ,StockUnit4
           ,OriginalBusinessDay
           ,OnHandQuantity
from tbTransactionStockCount where TransactionStockCountLocationID = @previousStockCountLocationId and ItemID = @itemid


SET @i = @i +1

end


