SQL Scenario of allocating ids to user
- by Enjoy coding
Hi,
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.
Please advice.
My environment is Teradata (ANSI SQL is enough). Thanks in advance.
