I have separated all the products of a company per warehouse in two db tables (givers and takers) with a certain logic that gives ranks to each record to be proccessed by its priority. I have ended up with two tables. The first table contains the givers and the second table contains the takers
I am strugling to create a result that will serially propose the needed movements from givers to takers. Is this possible to be handled only by SQL Cursor ? Any help would be apreciated ..
In future please provide consumable test data:
SET ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING ON;
GO
CREATE TABLE #Givers
(
Product char(1) NOT NULL
,QtyAllowedToGive int NOT NULL
,Warehouse_ID_Giver int NOT NULL
,[Rank] int NOT NULL PRIMARY KEY
);
INSERT INTO #Givers
VALUES ('A', 30, 100, 1)
,('A', 30, 101, 2)
,('A', 10, 102, 3)
,('B', 10, 200, 4)
,('B', 20, 201, 5)
,('D', 2, 300, 6);
CREATE TABLE #Takers
(
Product char(1) NOT NULL
,QtyAllowedToTake int NOT NULL
,Warehouse_ID_Taker int NOT NULL
,[Rank] int NOT NULL PRIMARY KEY
);
INSERT INTO #Takers
VALUES ('A', 50, 200, 1)
,('A', 5, 203, 2)
,('A', 11, 202, 3)
,('B', 16, 202, 4)
,('B', 3, 202, 5)
,('C', 10, 300, 6);One approach is to brute force the result with a number/tally table.
eg Using Create a Tally Function (fnTally) – SQLServerCentral
WITH Givers
AS
(
SELECT G.Product, G.Warehouse_ID_Giver, G.[Rank]
,ROW_NUMBER() OVER (PARTITION BY G.Product ORDER BY G.[Rank], N.N) AS rn
FROM #Givers G
JOIN dbo.fnTally(1, 1000) N
ON N.N <= G.QtyAllowedToGive
)
,Takers
AS
(
SELECT T.Product, T.Warehouse_ID_Taker, T.[Rank]
,ROW_NUMBER() OVER (PARTITION BY T.Product ORDER BY T.[Rank], N.N) AS rn
FROM #Takers T
JOIN dbo.fnTally(1, 1000) N
ON N.N <= T.QtyAllowedToTake
)
SELECT G.Product
,COUNT(*) AS QtyToMove
,G.Warehouse_ID_Giver, T.Warehouse_ID_Taker
FROM Givers G
JOIN Takers T
ON G.Product = T.Product
AND G.rn = T.rn
GROUP BY G.Product, G.Warehouse_ID_Giver, T.Warehouse_ID_Taker
ORDER BY G.Product, MAX(G.[Rank]), MAX(T.[Rank]);I cant find words to describe how professional is your answer. It works 100%
I wish i knew this site before. I will try to help also be answering questions that i can.
Thanks sencirelly.
I am glad it helped.
The downside of this approach is the potentially large number of intermediate rows to be processed. If the result is for an application I would be inclined to just to read the tables, with something like a .Net DataReader, and then loop through them to produce the result with something like C#. This will take the load off the database server. Also it is easier, and cheaper, to scale out application servers than to scale up a database server.
Yes, what you said is very interesting because this process will have to handle the proccessing of million rows.There are colours and sizes for each product that i didnt mentioned them in the example in order to keep it simple. The whole process will be executed from a client pc that runs an application server based software in which i am able to extend with VBdotNet code (Not C#). It would be a lot faster if the processing could run from the application server and not from the Azure database, but as i undestand then the fnTally function and the CTE queries will have to be converted to something like LINQ or something which will be a bit difficult for me.
If I were prototyping a .Net approach I would not expand the rows but use logic similar to a SQL cursor solution. The SQL number table approach has the advantage of being easy to understand, code and maintain. You may have to prototype a number of solutions and see what works best in your environment.
Also on the presentation layer of the app, will this be just some grid like report? How will it be used? Will therr be pagination implemented? Etc
The app is a client - server app. The user from a client in the presentation layer of the app, will select a set of distinct item codes using some filters (for example all codes of current season [spring 2022]) that will be aproximately some thousands distinct itemcodes .Then the user will select the stores ( aprox 30 warehouses) and from this combination because of the (color sizes combinations) on the backround there will be some millions rows for processing. By the book, all this load will be handled by the database server as @Ifor said...
After the processing of all these from the database server there will be a result as a proposal of movements sent from the the database server to the client (via Application Server) in a grid that i believe will contain some thousands of movements.
The user will be able at this phase to export the grid results in XLS and in the second phase the goal is for the movements to be entered automatically in the system as warehouse movement orders.
Its really interesting in a previous step how they separate the givers and the takers and rank them using sellout measurements per itemcode , color , size , warehouse but this is out of the scope of the topic!
Anyways, thanks for your help guys !