I have this challenge where I'm trying to figure out if I can write something in a single query rather than using a cursor.
The problem is I need to get items from a batch table based on a list of specified suppliers. Each supplier in this list has a weighting and this determines how many items out of the specified "TotalBatchSize" will be fetched per supplier.
My current solution is:
- I pass a "Supplier" list to a Stored Procedure
- Obtain Supplier weighting (say 50% for supplier1 for example) and apply this against the TotalBatchSize to Calculate a "SupplierItemCount"
- I then run a cursor over the "Supplier" list and on each iteration have a query that does a SELECT TOP(@SupplierItemCount) from the batch table and inserts those selected items into a temporary table
- I then return the temporary table after all the suppliers are been iterated over.
Does anyone know a way I can achieve this in a single query or a more efficient approach?
Very much welcome your thoughts!
CREATE PROCEDURE [Queue].[Pull] @QueueName varchar(100), @MaxNumberOfItems int = 1 -- number of packets to pick ,@SupplierIds [Configuration].SupplierIds READONLY AS BEGIN BEGIN TRY BEGIN TRAN CREATE TABLE #Suppliers(id int,Name varchar(20), [Priority] int,multiplier float,ItemsToFetch float); CREATE TABLE #Batch(BatchId int, QueueId int,BatchValue varchar(200),SupplierId int); -- Confirm table passed through is not empty and has more then one supplier IF(EXISTS (SELECT 1 FROM @SupplierIds)) BEGIN print 'Collection of Ids was Passed'; -- fetch supplier details for parsed SupplierIds INSERT INTO #Suppliers (id,Name,[Priority]) select id,Name,[Priority] from Configuration.Supplier where Id in (select Id from @SupplierIds) and IsActive=1 END ELSE BEGIN INSERT INTO #Suppliers (id,Name,[Priority]) select id,Name,[Priority] from Configuration.Supplier where IsActive=1 END -- add all the priority numbers together to determine the packets per supplier to fetch---------------------- DECLARE @TotalPriority int,@TotalSuppliers int; select @TotalPriority=SUM([Priority]),@TotalSuppliers=count(1) from #Suppliers; --select @TotalPriority as TotalPriority,@TotalSuppliers as TotalSuppliers; UPDATE #Suppliers SET multiplier=(SELECT CAST([Priority] AS float) / CAST(@TotalPriority AS float)) ,ItemsToFetch=(SELECT ROUND((SELECT CAST([Priority] AS float) / CAST(@TotalPriority AS float))*@MaxNumberOfItems,0)) -- due to rounding if 0 then fetch at least one item for supplier UPDATE #Suppliers SET ItemsToFetch=(case when ItemsToFetch=0 then 1 else ItemsToFetch end) -- LOOP Suppliers,fetch unqiue batch items per supplier--------------------------------------------- Declare @Id int, @ItemsToFetch int DECLARE Supplier_Cursor CURSOR FOR SELECT Id,ItemsToFetch FROM #Suppliers OPEN Supplier_Cursor FETCH NEXT FROM Supplier_Cursor INTO @Id,@ItemsToFetch; WHILE @@FETCH_STATUS = 0 BEGIN -- Only grab unique BatchValue from the top of the queue INSERT INTO #Batch(BatchId,QueueId,BatchValue,SupplierId) SELECT top(@ItemsToFetch) MIN(qi.BatchId) as BatchId,MIN(qi.Id) as QueueId,im.MetaValue AS BatchValue,i.SupplierId FROM [Queue].[QueueItem] qi inner join [Batch].[BatchMetadata] im on im.BatchId=qi.BatchId inner join [Batch].[Batch] i on i.Id=im.BatchId WHERE i.SupplierId=@Id and im.MetaValue not in (select BatchValue from #Batch) GROUP BY i.SupplierId,im.MetaValue order by QueueId; -- remove this after testing passed FETCH NEXT FROM Supplier_Cursor INTO @Id,@ItemsToFetch; END; CLOSE Supplier_Cursor; DEALLOCATE Supplier_Cursor; DROP TABLE #Suppliers; -- return final list SELECT * from #Batch DROP TABLE #Batch; COMMIT END TRY BEGIN CATCH Print 'Error from within Transaction'; IF @@TRANCOUNT >0 ROLLBACK TRAN END CATCH END