SQL Scenario of allocating ids to user

Question

I have an sql scenario as follows which I have been trying to improve.

There is a table 'Returns' which is having ids of the returned goods against a shop for an item. Its structure is as below.

Returns
-------------------------
Return ID | Shop | Item
-------------------------
  1         Shop1  Item1
  2         Shop1  Item1
  3         Shop1  Item1
  4         Shop1  Item1
  5         Shop1  Item1

There is one more table Supplier with Shop, supplier and Item as shown below.

Supplier
---------------------------------
Supplier | Shop | Item  | Volume
---------------------------------
  supp1    Shop1   Item1    20%
  supp2    Shop1   Item1    80%

Now as you see supp1 is supplying 20 % of total item1 volume and supp2 is supplying 80% of Item1 to shop1. And there were 5 return of items against the same Item1 for same Shop1. Now I need to allocate any four return IDs to Supp1 and remaining one return Id to supp2. This allocation of numbers is based on the ratio of the supplied volume percentage of the supplier. This allocation varies depending on the ratio of volume of supplied items.

Now I have tried a method of using RANKs as shown below by use of temp tables.

temp table 1 will have Shop, Return Id, Item, Total count of return IDs and Rank of the return id.

temp table 2 will have shop, Supplier, Item and his proportion and rank of proportion.

Now I am facing the difficulty in allocating top return ids to top supplier as illustrated above. As SQL doesnt have loops how can I achieve this. I have been tying several ways of doing this.

My environment is Teradata (ANSI SQL is enough).

Answer 1

UPDATE: You need to loop, here is some SQL code you can use as a starting point. Basically I use temporary tabes and ROW_NUMBER(). For my sample I used SQL SERVER 2008.

Try the following:

-- gather suppliers in temp table
DECLARE @SupplierTemp table  
        (  [RowId] int
          ,[Supplier] nvarchar (50)
          ,[ReturnCount] int ) 

-- gather supplier with return count
INSERT INTO @SupplierTemp
  SELECT ROW_NUMBER() OVER(ORDER BY [Supplier].[Supplier] DESC, [Supplier].[Supplier]) 
        ,[Supplier].[Supplier]
        , COUNT([Supplier].[Supplier])*[Supplier].[Volume]/100 AS ReturnCount 
  FROM [Supplier] 
  INNER JOIN [Returns] ON (([Returns].[Item] = [Supplier].[Item])  
                         AND ([Returns].[Shop] = [Supplier].[Shop])) 
  GROUP BY [Supplier].[Supplier], [Supplier].[Volume]
  ORDER BY [Supplier].[Supplier] 

-- gather returns in temp table
DECLARE @ReturnsTemp table  
       (  [RowId] int
         ,[Id] int) 

-- gather returns
INSERT INTO @ReturnsTemp
  SELECT ROW_NUMBER() OVER(ORDER BY [Returns].[Id] DESC, [Returns].[Id]) 
        ,[Returns].[Id]
  FROM [Returns] 

-- gather results in temp table
DECLARE @ResultsTemp table  
       (  [Supplier] nvarchar(50)
         ,[Id] int) 

DECLARE @rrowid as int
DECLARE @rid as int

-- loop over all suppliers
-- loop once for each [ReturnCount]
-- find the next avialable Id
DECLARE @srowid as int
DECLARE @loopCnt as int
DECLARE @supplier as nvarchar(50)

-- get first supplier   
SELECT @srowid = (SELECT MIN([RowId]) FROM @SupplierTemp)
SELECT @loopCnt = [ReturnCount] FROM @SupplierTemp WHERE [RowId] = @srowid
SELECT @supplier = [Supplier] FROM @SupplierTemp WHERE [RowId] = @srowid

-- loop over suppliers
WHILE @srowid IS NOT NULL
  BEGIN
    -- loop of number of returns    
    WHILE @loopCnt > 0
      BEGIN
        -- find the Id to return
        SELECT @rrowid = (SELECT MIN([RowId]) FROM @ReturnsTemp)
        SELECT @rid = [Id] FROM @ReturnsTemp WHERE [RowId] = @rrowid

        INSERT INTO @ResultsTemp VALUES (@supplier, @rid)

        DELETE FROM @ReturnsTemp WHERE [RowId] = @rrowid

        SELECT @loopCnt = @loopCnt - 1
      END

    -- delete current item from table to keep loop moving forward...      
    DELETE FROM @SupplierTemp WHERE [RowId] = @srowid

    -- get next supplier.
    SELECT @srowid = (SELECT MIN([RowId]) FROM @SupplierTemp)
    SELECT @loopCnt = [ReturnCount] FROM @SupplierTemp WHERE [RowId] = @srowid
    SELECT @supplier = [Supplier] FROM @SupplierTemp WHERE [RowId] = @srowid
  END

SELECT * FROM @ResultsTemp