SQL Server: query to aggregate considering minimum quantity

Question

Hello,

I need to generate a stored procedure in SQL Server in order to aggregate customer orders (ordered by date) of the same article until they reach a minimum quantity value.

As an example:

Article 1:

• CustomerOrder1: 2
• CustomerOrder2: 9
• CustomerOrder3: 12
• CustomerOrder4: 2
• CustomerOrder5: 3
• CustomerOrder6: 5
• CustomerOrder7: 3
• CustomerOrder8: 4

Let's say there is a minimum quantity of 10 pieces, that can be different for each article.

The stored procedure should populate two tables.

The table of production orders:

• ProductionOrder1: 2+9 = 11
• ProductionOrder2: 12
• ProductionOrder3: 2+3+5 = 10
• ProductionOrder4: 3+4 = 7

The table of link between CustomerOrders and ProductionOrders:

• ProductionOrder1 - CustomerOrder1
• ProductionOrder1 - CustomerOrder2
• ProductionOrder2 - CustomerOrder3
• ProductionOrder3 - CustomerOrder4
• ProductionOrder3 - CustomerOrder5
• ProductionOrder3 - CustomerOrder6
• ProductionOrder4 - CustomerOrder7
• ProductionOrder4 - CustomerOrder8

The same action should be done for all the other articles.

Any ideas on how to solve this? Can you please provide a sample code? I'm not really experienced with cursors and variables, so any help would be really appreciated.

Thanks in advance

Mattia

Answer 1

Here is an outline. The first part sets up the tables and add the sample data. Preferably, you should have included this yourself.

SET NOCOUNT ON
go
CREATE TABLE customerorder (orderid int NOT NULL,
                            date    date NOT NULL,
                            qty     int  NOT NULL,
                            prodorder int NULL,
                            CONSTRAINT pk_customerorder PRIMARY KEY (orderid)
)
INSERT customerorder(orderid, date, qty)
  VALUES(1, '20200101', 2),
        (2, '20200103', 9),          
        (3, '20200104', 12),          
        (4, '20200105', 2),          
        (5, '20200106', 3),          
        (6, '20200107', 5),          
        (7, '20200108', 3),          
        (8, '20200109', 4)
go
-- Solution starts here.
DECLARE @prodorder int = 1,
        @sum       int = 0,
        @orderid   int,
        @qty       int,
        @threshold int = 10,
        @cur CURSOR

SET @cur = CURSOR STATIC FOR
   SELECT orderid, qty
   FROM   customerorder
   ORDER  BY date, qty

OPEN @cur 

WHILE 1 = 1 
BEGIN
   FETCH @cur INTO @orderid, @qty
   IF @@fetch_status <> 0
      BREAK

   UPDATE customerorder
   SET    prodorder = @prodorder
   WHERE  orderid = @orderid

   SET @sum += @qty

   IF @sum >= @threshold
      SELECT @prodorder += 1,@sum = 0
END
go
SELECT * FROM customerorder
go

DROP TABLE customerorder