How to show order fulfilment in a SQL Server 2008 query

Question

I am trying to think of a way on a SQL Server 2008 database to run through a sales order table and get open demand for a part, order it by due date, then look at a purchase order table and fulfill the sales orders by PO, ordering the PO supply by due date as well. At the same time, I need to show what PO(s) are fulfilling the sales order.

For example:

SO table

SO#     DueDate     Part Number  Required QTY
---------------------------------------------
100     9/3/16      1012          2
101     9/12/16     1012          1
107     10/11/16    1012          4
103     10/17/16    1012          7

PO table:

PO#     DueDate     Part Number  Ordered QTY
--------------------------------------------
331     9/1/16      1012          1
362     9/2/16      1012          1
359     9/24/16     1012          5
371     10/1/16     1012          3
380     10/10/16    1012          10

With this data, I would like to see this result:

SO#  DueDate     Part Number  Required QTY  PO number  QTY Used  QTY Remain
 --------------------------------------------------------------------------
100  9/3/16      1012          2             331         1         0 
100  9/3/16      1012          1             362         1         0
101  9/12/16     1012          1             359         1         4
107  10/11/16    1012          4             359         4         0
103  10/17/16    1012          7             371         3         0
103  10/17/16    1012          7             380         4         6

I have done this sales order fulfillment process before, but not to the point of breaking down what PO(s) are fulfilling the order, only to the point of summing all open supply, then running through and subtracting the supply from each sales order to get a running balance of supply left.

Many thanks in advance for your help.

The logic for the output table, especially `Qty Remain` isn't clear. — Ash, Aug 25 '16 at 03:38
If you want an answer, I suggest you use SQLFiddle.com to set up an example we can play with. The approach I would use is to write one query that applies the next 'remaining' purchase order in the queue to the next remaining sales order in the queue, then just rerun that query till you run out of SO's or PO's. You'll need a working table which snapshots both tables and records working amounts, as well as another table to record the final amount. — Nick.Mc, Aug 25 '16 at 03:55
@AshwinNair All the output `QTY` columns are a running total. Look at each row as if the PO has fulfilled as much of the SO as it can, we record how many parts were used and how many are still required if the PO didn't contain enough parts or how many parts remain in the PO if not all of them were used. The next row is then either the effect of the next PO on an incomplete SO, or the next SO if the current SO is completely fulfilled. — iamdave, Aug 25 '16 at 09:51

gofr1 · Accepted Answer · 2016-08-27T12:29:04.003

I found a bit weird solution, hope it helps you. Maybe later I could optimize it, but now I post it as is:

;WITH cte AS (
    SELECT 1 as l
    UNION ALL
    SELECT l+1
    FROM cte
    WHERE l <= 1000000
), SO_cte AS (
    SELECT  *,
            ROW_NUMBER() OVER (ORDER BY DueDate ASC) as rn
    FROM SO s
    CROSS JOIN cte c
    WHERE c.l <= s.[Required QTY]
), PO_cte AS (
    SELECT  *,
            ROW_NUMBER() OVER (ORDER BY DueDate ASC) as rn
    FROM PO p
    CROSS JOIN cte c
    WHERE c.l <= p.[Ordered QTY]
), almost_done AS (
    SELECT DISTINCT     
            s.SO#,
            s.DueDate,
            s.[Part Number],
            p.PO#,
            s.[Required QTY],
            p.[Ordered QTY]
    FROM SO_cte s
    LEFT JOIN PO_cte p
        ON p.rn = s.rn
), final AS (
    SELECT  *,
            ROW_NUMBER() OVER (ORDER BY DueDate) AS RN
    FROM almost_done
)

SELECT  f.SO#,
        f.DueDate,
        f.[Part Number],
        f.[Required QTY],
        f.PO#,
        CASE WHEN f.[Ordered QTY]>f.[Required QTY] 
                THEN ISNULL(ABS(f1.[Required QTY]-f1.[Ordered QTY]),f.[Required QTY]) 
                ELSE f.[Ordered QTY] END 
                        as [QTY Used],
        f.[Ordered QTY] - 
        CASE WHEN f1.PO# = f.PO# 
                THEN f1.[Ordered QTY]
                ELSE
                    CASE WHEN f.[Ordered QTY]>f.[Required QTY] 
                            THEN ISNULL(ABS(f1.[Required QTY]-f1.[Ordered QTY]),f.[Required QTY]) 
                            ELSE f.[Ordered QTY] END
         END as [QTY Remain]
FROM final f
LEFT JOIN final f1
    ON f.RN = f1.RN+ 1
        AND (f.SO# = f1.SO# OR f.PO# = f1.PO#)
OPTION(MAXRECURSION 0)

Output for data you provided:

SO# DueDate     Part Number Required QTY    PO# QTY Used    QTY Remain
100 2016-09-03  1012        2               331 1           0
100 2016-09-03  1012        2               362 1           0
101 2016-09-12  1012        1               359 1           4
107 2016-10-11  1012        4               359 4           0
103 2016-10-17  1012        7               371 3           0
103 2016-10-17  1012        7               380 4           6

@Clement At first it populates each row of SO an PO as much times as they have in *Qty column and sort them by DueDate. Then both sorted tables are joined by row_number so each 1 Qty from SO is matched to 1 Qty in PO and we take distinct values and add row_numbers to that recordset. Then we match this recordset to itself joining by current and next row where SO id or PO id are identical, and count Used and Remain quantities... Actually it was 4 years ago, man :) and it was pretty raw solution f.e. CTE with 1000000 rows... — gofr1, Oct 07 '20 at 14:57
@Clement you can check it here http://sqlfiddle.com/#!18/ad2d0/6 — gofr1, Oct 07 '20 at 14:58

How to show order fulfilment in a SQL Server 2008 query

1 Answers1

Linked