CODEWITHSUNDEEP
sqlsql-serverdatabase
Awer Muller
Awer Muller

Reputation: 627

Splitting quantities to fulfill orders - Set based query

I'm trying to find a set based method to do the following:

Supose I have only one kind of product in my warehouse. This product comes in boxes of different sizes. I have to fulfill the orders using my stock, spliting the containers as needed to achieve the exact ordered quantity. Both orders and stock items have to be processed FIFO.

As an example, here are some values and the solution (computed by hand).

+---------+-------+  +-----------+-------+
| OrderId | OQtty |  | Container | CQtty |
+---------+-------+  +-----------+-------+
|       1 | 87000 |  |         1 | 10000 |
|       2 | 26500 |  |         2 | 10000 |
|       3 | 12000 |  |         3 | 10000 |
|       4 | 43600 |  |         4 | 10000 |
|       5 |  3000 |  |         5 | 10000 |
|         |       |  |         6 |  5000 |
|         |       |  |         7 | 10000 |
|         |       |  |         8 | 10000 |
|         |       |  |         9 | 10000 |
|         |       |  |        10 | 10000 |
|         |       |  |        11 | 10000 |
|         |       |  |        12 | 10000 |
|         |       |  |        13 | 10000 |
|         |       |  |        14 |  2500 |
|         |       |  |        15 |  2500 |
|         |       |  |        16 | 10000 |
|         |       |  |        17 | 10000 |
|         |       |  |        18 | 10000 |
|         |       |  |        19 | 10000 |
|         |       |  |        20 | 10000 |
+---------+-------+  +-----------+-------+

+---------+-----------+-------+---------+
| OrderId | Container | CQtty | Running |
+---------+-----------+-------+---------+
|       1 |         1 | 10000 |   10000 |
|       1 |         2 | 10000 |   20000 |
|       1 |         3 | 10000 |   30000 |
|       1 |         4 | 10000 |   40000 |
|       1 |         5 | 10000 |   50000 |
|       1 |         6 |  5000 |   55000 |
|       1 |         7 | 10000 |   65000 |
|       1 |         8 | 10000 |   75000 |
|       1 |         9 | 10000 |   85000 |
|       1 |        10 |  2000 |   87000 |
|       2 |        10 |  8000 |    8000 |
|       2 |        11 | 10000 |   18000 |
|       2 |        12 |  8500 |   26500 |
|       3 |        12 |  1500 |    1500 |
|       3 |        13 | 10000 |   11500 |
|       3 |        14 |   500 |   12000 |
|       4 |        14 |  2000 |    2000 |
|       4 |        15 |  2500 |    4500 |
|       4 |        16 | 10000 |   14500 |
|       4 |        17 | 10000 |   24500 |
|       4 |        18 | 10000 |   34500 |
|       4 |        19 |  9100 |   43600 |
|       5 |        19 |   900 |     900 |
|       5 |        20 |  2100 |    3000 |
+---------+-----------+-------+---------+

Edit: I forgot to mention this is green field sql server 2012. Thanks

Upvotes: 1

Views: 340

Answers (2)

Anon
Anon

Reputation: 10918

WITH
  O AS (SELECT *,SUM(OQtty) OVER(ORDER BY OrderId) Ort FROM @Orders),
  C AS (SELECT *,SUM(CQtty) OVER(ORDER BY Container) Crt FROM @Containers)
SELECT
  OrderId, Container, CQtty,
  CASE WHEN Crt < Ort THEN Crt-Ort+OQtty ELSE Oqtty END AS Running
FROM O
INNER JOIN C ON Crt > Ort-OQtty AND Crt < Ort+CQtty
ORDER BY OrderId, Container

Demo: http://sqlfiddle.com/#!6/cb038/1

This is a Partition Alignment problem. It's the same type of problem as trying to match the countries of North and South America with the countries of Europe and Africa based on which ones have Atlantic coastlines directly East-West from each other. The solution should be agnostic to which group is actually East and which group is actually West. All that is needed is this logic:

If the difference between the latitudes of the southern extremes of two countries is less than the latitudinal 'height' of either country, then they overlap

Likewise, in this problem the 'containers' and 'orders' are fully interchangeable. The only thing that matters is the difference between the running totals. If ABS(Crt - Ort) < MAX(OQtty, CQtty), then that container pairs with that order.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271023

This can be done using window functions, but it is a bit tricky. The reason it can be done is because you are counting the total quantity. In your example, part of container 8 goes to order 1 and part to order 2. Believe or not, this is much easier than if part of container 8 goes to order 1 . . . and the rest is wasted.

The idea is simple. Do a cumulative sum on the containers and orders. Then use a non-equijoin to find the containers used for each order -- this is where the cumulative sums overlaps. Then do some more finagling to handle the boundaries. Here is an idea of how to do this:

with oq as (
      select o.*,
             sum(oqty) over (order by orderid) - oqty as sumoqty_start,
             sum(oqty) over (order by orderid) as sumoqty_end
      from orders o
     ),
     cq as (
      select c.*,
             sum(cqty) over (order by container) - cty as sumcqty_start, 
             sum(cqty) over (order by container) as sumcqty_end
      from contains c
     ),
     oc as (
      select o.orderid, o.sumoqty_start, o.sumoqty_end,
             c.container, c.sumcqty_start, c.sumcty_end
      from oq join
           cq
           on oc.sumoqty_start < sumcqty_end and
              oc.sumoqty_end > sumcqty_start
     )
select o.orderid, c.containerid,
       ((case when sumcqty_end < sumoqty_end then sumcqty_end else sumoqty_end end) -
        (case when sumcqty_start > sumoqty_start then sumcqty_start else sumoqty_start end)
       ) as OrderContainerAmount
from oc
order by 1, 2;

I think this is right. Without a SQL Fiddle, I'm not able to test it.

Upvotes: 0

Related Questions