Click here to Skip to main content
15,887,746 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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:

SQL
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 /*WHILE*/
Posted
Updated 3-Jun-16 3:55am
v2
Comments
Tomas Takac 3-Jun-16 10:09am    
What you have there in case #2 is an optimization problem, probably a variation of Knapsack. This is not trivial to implement and I certainly wouldn't do it in SQL.
Abrar Kazi 4-Jun-16 23:12pm    
So what do you suggest?
Tomas Takac 5-Jun-16 2:50am    
You need to do a lot of reading and learn how to solve problems like this. Wikipedia[^] is a good place to start. I cannot possibly answer this question to your or my satisfaction. For example you didn't even specify what the result should be in your example. To get Qty 14 you could just take B1 and B2 which equals to 15 which in turn is within the 10% margin and be done with it. But exact match is better so B3 + B5 is what you want. In a small set like yours in this example you can generate all the possible combinations and calculate the Qty for each of them then pick the best one. You can start with depth-first search[^] for example. As you tree won't ever be deeper than 3 levels this might work quite well for you. The problem is that this doesn't scale well and for bigger sets you need to employ some of the more sophisticated algorithms. The bottom line is, you need to implement it and test it. And I suggest you don't implement this in the database as SQL is not the best language for such tasks. Believe me, I did it. General-purpose languages like C# is better suited for such job.
Abrar Kazi 7-Jun-16 1:09am    
Thanks Tomas agreed to your suggestion. Its quite an easy task using loops in C#. Thanks.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900