BATCH : Qty : ProductID
B1 : 10 : 1
B2 : 5 : 1
B3 : 13 : 1
B4 : 2 : 1
B5 : 1 : 1
I will pass Required Qty say 14
I want my query to find those batches whose qty ;
CAse 1 = Equals required qty
Case 2 = max combination of 3 or 2 batches makes it equal to required qty or brings sum of qty nearby to -5% of req qty or +10% of req qty
What I have tried:
DECLARE checkQtyCursor CURSOR FOR
SELECT BATCH,Qty,HOMEIDEA_PRODUCT_ID FROM #TempStockVO ORDER BY QTY DESC
OPEN checkQtyCursor
FETCH NEXT FROM checkQtyCursor into @Cur_batch,@Cur_qty,@Cur_HIPD
WHILE (@@FETCH_STATUS = 0 AND @Cur_qty > 0)
BEGIN
PRINT 'IN CURSOR'
IF (@Cur_qty = @RequiredQty)
BEGIN
INSERT INTO #TempCursorTable (FromBatch,AvailableQty,HomeIdeaProductID)
SELECT @Cur_batch, @Cur_qty,@Cur_HIPD
SET @ErrorMessage= 'Available In Single Piece'
BREAK;
END
ELSE IF (@Cur_qty > @RequiredQty)
BEGIN
INSERT INTO #TempCursorTable (FromBatch,AvailableQty,HomeIdeaProductID)
SELECT @Cur_batch, @Cur_qty,@Cur_HIPD
SET @ErrorMessage= 'Available In Single Piece'
BREAK;
END
ELSE IF(@Cur_qty<@RequiredQty AND @TempQty<@RequiredQty)
BEGIN
SET @ErrorMessage='Available In Multiple Pieces'
INSERT INTO #TempCursorTable (FromBatch,AvailableQty,HomeIdeaProductID)
SELECT @Cur_batch, @Cur_qty,@Cur_HIPD
SET @TempQty = @TempQty+@Cur_qty
SET @i=@i+1;
IF(@TempQty>=@RequiredQty AND @i>3)
BEGIN
SET @ErrorMessage = 'Exceeded 3 Cut Pieces'
BREAK;
END
END
FETCH NEXT FROM checkQtyCursor INTO @Cur_batch,@Cur_qty,@Cur_HIPD
END