SQL Server: query to aggregate considering minimum quantity

Mattia M 1 Reputation point
2022-03-17T10:08:55.113+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,917 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,564 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Erland Sommarskog 102.4K Reputation points
    2022-03-17T23:07:17.073+00:00

    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
    
    0 comments No comments