

declare
     @EntityId BIGINT = 875
 , @VendorItemCode VARCHAR(10) = '030002' 
 , @AllocationStart DATE  = '20200212'
 , @AllocationEnd DATE = '20200225'
 , @AllocationExists VARCHAR(MAX)

  
 DECLARE @allocationMatches TABLE (AllocationId INT, AllocationStart DATE)  
  
select * from tbAllocation with(nolock)
where EntityId = @EntityId
and VendorItemCode = @VendorItemCode

 INSERT INTO @allocationMatches  
 SELECT AllocationId  
  , AllocationStart  
 FROM [dbo].[tbAllocation] WITH(NOLOCK)  
 WHERE EntityId = @EntityId  
 AND VendorItemCode = @VendorItemCode  
 AND (  
  ( AllocationStart >= @AllocationStart AND AllocationStart < @AllocationEnd )  
  OR ( AllocationEnd > @AllocationStart AND AllocationEnd <= @AllocationEnd )  
  OR ( AllocationStart <= @AllocationStart AND AllocationEnd >= @AllocationEnd )  
 ) AND AllocationStart != AllocationEnd
  
select * from @allocationMatches

 DECLARE @overlappingAllocations INT  
  
 SELECT @overlappingAllocations = COUNT(*)  
 FROM @allocationMatches  
    
 IF ( @overlappingAllocations >= 1 )  
 BEGIN  
    
  SET @AllocationExists = 'The start and end dates overlap multiple existing allocations.'  
 END  
 ELSE IF ( @overlappingAllocations = 1 )  
 BEGIN  
  
  IF EXISTS ( SELECT 1 FROM @allocationMatches WHERE AllocationStart < CONVERT(DATE, GETDATE())) --'20200206' )  -- 
  BEGIN  
   
   SET @AllocationExists = 'An allocation has already started for this item. Stop the existing allocation for this product first.'  
  END  
 END  
 
select @AllocationExists


select * from tbAllocation with(nolock)
where AllocationId = 278 --8216

select * from tbAllocationDetail where AllocationId = 278

update tbAllocation set AllocationEnd = '20200210'
where AllocationId = 278 and VendorItemCode = '030002'

