0

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
  • 3
    What RDBMS? In SQL Server this should be easily done with CTEs and windowing functions like ROW_NUMBER(). – JNK May 6 '15 at 14:10
  • 1
    Can you share the current procedure? – Chris Woods May 6 '15 at 14:14
  • @JNK Version is Microsoft SQL Server 2012 (SP1) - 11.0.3401.0 (X64) – Geoffrey Hall May 6 '15 at 14:44
0

I think something with CROSS APPLY would work well for you. Look at this example:

DECLARE @tbl_Supplier TABLE (ID INT PRIMARY KEY, SupplierName VARCHAR(25),Weighting FLOAT)
INSERT INTO @tbl_Supplier
VALUES  (1,'Supplier1',.5),(2,'Supplier2',.25 ),(3,'Supplier3',.25);

DECLARE @Items TABLE(SupplierID INT,ItemName VARCHAR(25))
INSERT INTO @Items
VALUES  (1,'Item1'),(1,'Item2'),(1,'Item3'),(2,'Item1'),(2,'Item2'),(3,'Item2'),(3,'Item3');

DECLARE @TotalBatchSize INT = 4;

SELECT  SupplierName,
        CA.ItemName,
        S.Weighting * @TotalBatchSize AS num_of_rows_to_be_returned
FROM @tbl_Supplier AS S
CROSS APPLY (SELECT TOP(CAST(@TotalBatchSize * S.Weighting AS INT)) ItemName FROM @Items I WHERE I.SupplierID = S.ID) CA

Results:

SupplierName              ItemName                  num_of_rows_to_be_returned
------------------------- ------------------------- --------------------------
Supplier1                 Item1                     2
Supplier1                 Item2                     2
Supplier2                 Item1                     1
Supplier3                 Item2                     1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.