
-- Step 1, enter your store 
DECLARE @entityId AS BIGINT = 
DECLARE @username as varchar(50) = ''
 
-- Step 2, find the TransactionStockCountLocationId you just made
DECLARE @transactionStockCountLocationId AS BIGINT = (
	select top 1 TransactionStockCountLocationID from tbTransactionStockCountLocation 
	where EntityID = @entityId and ClerkName like @username and Status = 'Applied' order by ApplyDate desc)

DECLARE @daysInCalculation AS BIGINT = 90
DECLARE @CurrentDate DATETIME = CONVERT (DATE, GETDATE()) 
DECLARE @NumberOfDays INT = @daysInCalculation - 1

declare @StartDate DateTime = dateadd(day, -@NumberOfDays,@CurrentDate) 
declare @EndDate DateTime = dateadd(day, -1,@CurrentDate) 

-- Step 3, make sure this store is NOT configured to import UPT Qty
select case when dbo.fn_DoesStoreImportUpt (@entityId) = 1 then 'Imports UPT' else 'Calculates UPT' end as [Store Settings],
	@StartDate as [Start of Date Range],
	@EndDate as [End of Date Range],
	@TransactionStockCountLocationId as [TransactionStockCountLocationId]
	
-- Step 4, find the items in the count
declare @itemidlist varchar(max)
select @itemidlist = coalesce(@itemidlist+',' , '') +  CAST(itemid as varchar(100)) from tbTransactionStockCount with (nolock) 
where TransactionStockCountLocationID = @transactionStockCountLocationId

DECLARE @Itemids TABLE (ItemID INT)

INSERT INTO @Itemids
SELECT value FROM dbo.fn_split(@itemidlist, ',')
 
select ItemID as [Item Ids in the Count] from @Itemids
  
--Step 5, find entries in tbDailyStockBalance for those items in the businesss range
DECLARE @InventoryActivities3 TABLE (EntityID BIGINT, BusinessDay SMALLDATETIME, ItemId BIGINT, PurchaseExclReturnsQty FLOAT, PurchaseReturnsQty FLOAT, TransferOutQty FLOAT, TransferInQty FLOAT)

INSERT INTO @InventoryActivities3  
SELECT dsb.EntityID,  
         dsb.BusinessDay,  
         dsb.ItemId,  
   dsb.PurchaseExclReturnsQty,  
   dsb.PurchaseReturnsQty,  
   dsb.TransferOutQty,  
   dsb.TransferInQty  
 FROM tbDailyStockBalance dsb WITH (NOLOCK)   
   WHERE dsb.EntityID = @EntityID  
     AND dsb.BusinessDay BETWEEN @StartDate AND @EndDate  
     AND dsb.ItemID IN (SELECT ItemID FROM @Itemids)  

select distinct  '' as [Inventory Activity In Date Range], * from @InventoryActivities3 
where PurchaseExclReturnsQty > 0 or PurchaseReturnsQty > 0 or TransferOutQty > 0 or TransferInQty > 0
order by ItemId, BusinessDay 
 
-- Step 6, Let's combine the start and end counts the way the stored proc does
  select '' as [Start and End Count Quantities and Dates],
		 tsc.ItemID, 
		 StartCount.StockCount StartCountQty,
		 EndCount.StockCount EndCountQty, 
		 min(tsc.BusinessDay) FirstCountDate, 
		 max(EndCount.BusinessDay) LastCountDate
    from tbTransactionStockCount tsc with (nolock)
    join tbTransactionStockCountLocation tscl with (nolock) on tsc.TransactionStockCountLocationID = tscl.TransactionStockCountLocationID and tscl.AutoGenerated <> 1
	 left join (select tsc.EntityID,   
                      tsc.ItemID,  
       tsc.StockCount,   
       ROW_NUMBER() over (PARTITION by tsc.EntityID, tsc.ItemID order by tsc.businessday, tsc.applydate) as seqnum  
              from tbTransactionStockCount tsc with (nolock)  
                 join tbTransactionStockCountLocation tscl with (nolock) on tsc.TransactionStockCountLocationID = tscl.TransactionStockCountLocationID and tscl.AutoGenerated <> 1  
                where tsc.EntityID = @EntityID  
                  and tsc.BusinessDay between @StartDate and @EndDate  
                  and tsc.ItemID in (select ItemID from @Itemids)
                  and tsc.TransactionStockCountID <> @transactionStockCountLocationId
           ) StartCount on tsc.EntityID = StartCount.EntityID   
                 and tsc.ItemID = StartCount.ItemID  
        and StartCount.seqnum = 1  
 left join (select tsc.EntityID,   
                      tsc.ItemID,  
       tsc.StockCount, tsc.BusinessDay
              from tbTransactionStockCount tsc with (nolock)  
                 join tbTransactionStockCountLocation tscl with (nolock) on tsc.TransactionStockCountLocationID = tscl.TransactionStockCountLocationID
                where tscl.TransactionStockCountLocationID = @transactionStockCountLocationId
           ) EndCount on tsc.EntityID = EndCount.EntityID   
                 and tsc.ItemID = EndCount.ItemID  
   where tsc.EntityID = @EntityID  
     and tsc.BusinessDay between @StartDate and @EndDate  
     and tsc.ItemID in (select ItemID from @Itemids)
   group by tsc.EntityID, tsc.ItemID, StartCount.StockCount,EndCount.StockCount  


-- Step 7, add in the sales data and get the new UPT Qtys
select '' as [New vs Old UPT Qty], ei.ItemID, ei.UptQty,
	case when sum(tcd.Sales_Excl_GST) = 0 then 0 else (sc.StartCountQty - sc.EndCountQty + sum(- dsb.TransferOutQty + dsb.TransferInQty + dsb.PurchaseExclReturnsQty)) / sum(tcd.Sales_Excl_GST) * 1000 end CalculatedActualUPTQty,
	'=' as [Formula for UPT:], '(', sc.StartCountQty, '-', sc.EndCountQty, '+', sum(- dsb.TransferOutQty + dsb.TransferInQty + dsb.PurchaseExclReturnsQty) as [Transfers & Orders], ')', '/', sum(tcd.Sales_Excl_GST) as [Sales], '*',  '1000'
  from @InventoryActivities3 dsb

  join tbEntityItem ei with (nolock) on dsb.EntityID = ei.EntityID
                               and dsb.ItemId = ei.ItemID
							   and ei.UPTAutoCalc = 1 
							   and ei.InUse = 1
  join tbTransactionCashDetail tcd with (nolock) on dsb.EntityID = tcd.EntityID
                                           and dsb.BusinessDay = tcd.StartDate
  join (
  select tsc.EntityID, 
         tsc.ItemID, 
		 min(tsc.BusinessDay) FirstCountDate, 
		 max(EndCount.BusinessDay) LastCountDate,
		 StartCount.StockCount StartCountQty,
		 EndCount.StockCount EndCountQty 
    from tbTransactionStockCount tsc with (nolock)
    join tbTransactionStockCountLocation tscl with (nolock) on tsc.TransactionStockCountLocationID = tscl.TransactionStockCountLocationID and tscl.AutoGenerated <> 1
	 left join (select tsc.EntityID,   
                      tsc.ItemID,  
       tsc.StockCount,   
       ROW_NUMBER() over (PARTITION by tsc.EntityID, tsc.ItemID order by tsc.businessday, tsc.applydate) as seqnum  
              from tbTransactionStockCount tsc with (nolock)  
                 join tbTransactionStockCountLocation tscl with (nolock) on tsc.TransactionStockCountLocationID = tscl.TransactionStockCountLocationID and tscl.AutoGenerated <> 1  
                where tsc.EntityID = @EntityID  
                  and tsc.BusinessDay between @StartDate and @EndDate  
                  and tsc.ItemID in (select ItemID from @Itemids)
           ) StartCount on tsc.EntityID = StartCount.EntityID   
                 and tsc.ItemID = StartCount.ItemID  
        and StartCount.seqnum = 1  
 left join (select tsc.EntityID,   
                      tsc.ItemID,  
       tsc.StockCount, tsc.BusinessDay
              from tbTransactionStockCount tsc with (nolock)  
                 join tbTransactionStockCountLocation tscl with (nolock) on tsc.TransactionStockCountLocationID = tscl.TransactionStockCountLocationID
                where tscl.TransactionStockCountLocationID = @transactionStockCountLocationId
           ) EndCount on tsc.EntityID = EndCount.EntityID   
                 and tsc.ItemID = EndCount.ItemID  
   where tsc.EntityID = @EntityID  
     and tsc.BusinessDay between @StartDate and @EndDate  
     and tsc.ItemID in (select ItemID from @Itemids)
   group by tsc.EntityID, tsc.ItemID, StartCount.StockCount,EndCount.StockCount  
 ) sc on dsb.EntityID = sc.EntityID 
                                         and dsb.ItemId = sc.ItemID
										 and dsb.BusinessDay > sc.FirstCountDate
										 and dsb.BusinessDay <= sc.LastCountDate 
 group by ei.EntityID, ei.ItemID, ei.UPTAutoCalc, ei.UPTQty, sc.StartCountQty, sc.EndCountQty
 
 
