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
Best Answer
I think something with CROSS APPLY would work well for you. Look at this example:
Results: