Hello,
I need some help and advise on how to write a query in order to
allocate parts in stock to purchase orders.
Suppose you have in stock different batches for a given part P, like:

B1 100 pcs
B2 50 pcs
B3 75 pcs

Where B1, B2 and B3 are the batch numbers for part P

Suppose you have purchase orders for P, like:

PO1 20
PO2 100
PO3 100

Where PO1, PO2 and PO3 are purchase orders numbers in quantity 20,100
and 100 respectively.

Now I need to allocate the batches of P to the orders for P so that
each order is complete or the parts are finished.

Something like:

PO1 B1 20 (PO1 is complete with 20 pcs of B1)
PO2 B1 80 (
PO2 B2 20 (PO2 is complete with 80 pcs of B1 and 20 pcs of B2. B1 has
been all allocated)
PO3 B2 30 (B2 has been all allocated)
PO3 B3 70 (PO3 is complete with 30 pcs of B2 and 70 pcs of B3)

5 pcs of B3 are left in stock after the allocation

Any idea ??

An example:

------------------------------ Commands Entered
------------------------------
WITH
stock(part_no , batch_no , quantity) AS (
VALUES
( 'S01' , 'B1' , 100 )
, ( 'S01' , 'B2' , 50 )
, ( 'S01' , 'B3' , 75 )
)
, purchase_order(order_no , part_no , quantity) AS (
VALUES
( 'PO1' , 'S01' , 20 )
, ( 'PO2' , 'S01' , 100 )
, ( 'PO3' , 'S01' , 100 )
)
SELECT p.order_no
, s.batch_no
, MIN( p.sum_order - s.lag_stock
, s.sum_stock - p.lag_order
) AS quantity
FROM (SELECT p.*
, SUM( quantity )
OVER( PARTITION BY part_no
ORDER BY order_no ) sum_order
, LAG( SUM(quantity)
OVER( PARTITION BY part_no
ORDER BY order_no )
, 1 , 0
)
OVER( PARTITION BY part_no
ORDER BY order_no ) lag_order
FROM purchase_order p
) p
LEFT OUTER JOIN
(SELECT s.*
, SUM(quantity)
OVER( PARTITION BY part_no
ORDER BY batch_no ) sum_stock
, LAG( SUM(quantity)
OVER( PARTITION BY part_no
ORDER BY batch_no )
, 1 , 0
)
OVER( PARTITION BY part_no
ORDER BY batch_no ) lag_stock
FROM stock s
) s
ON s.part_no = p.part_no
AND s.sum_stock > p.lag_order
AND s.lag_stock < p.sum_order
ORDER BY
p.order_no
, s.batch_no
;
------------------------------------------------------------------------------

ORDER_NO BATCH_NO QUANTITY
-------- -------- -----------
PO1 B1 20
PO2 B1 80
PO2 B2 20
PO3 B2 30
PO3 B3 70

5 record(s) selected.

Another much shorter example:

------------------------------ Commands Entered
------------------------------
WITH
stock(part_no , batch_no , quantity) AS (
VALUES
( 'S01' , 'B1' , 100 )
, ( 'S01' , 'B2' , 50 )
, ( 'S01' , 'B3' , 75 )
)
, purchase_order(order_no , part_no , quantity) AS (
VALUES
( 'PO1' , 'S01' , 20 )
, ( 'PO2' , 'S01' , 100 )
, ( 'PO3' , 'S01' , 100 )
)
SELECT p.order_no
, s.batch_no
, MIN( p.sum_order - s.sum_stock + s.quantity
, s.sum_stock - p.sum_order + p.quantity
) AS quantity
FROM (SELECT p.*
, SUM(quantity)
OVER( PARTITION BY part_no
ORDER BY order_no ) sum_order
FROM purchase_order p
) p
LEFT OUTER JOIN
(SELECT s.*
, SUM(quantity)
OVER( PARTITION BY part_no
ORDER BY batch_no ) sum_stock
FROM stock s
) s
ON s.part_no = p.part_no
AND p.sum_order > s.sum_stock - s.quantity
AND s.sum_stock > p.sum_order - p.quantity
ORDER BY
p.order_no
, s.batch_no
;
------------------------------------------------------------------------------

ORDER_NO BATCH_NO QUANTITY
-------- -------- -----------
PO1 B1 20
PO2 B1 80
PO2 B2 20
PO3 B2 30
PO3 B3 70

5 record(s) selected.

Another shorter example:

------------------------------ Commands Entered
------------------------------
WITH
stock(part_no , batch_no , quantity) AS (
VALUES
( 'S01' , 'B1' , 100 )
, ( 'S01' , 'B2' , 50 )
, ( 'S01' , 'B3' , 75 )
)
, purchase_order(order_no , part_no , quantity) AS (
VALUES
( 'PO1' , 'S01' , 20 )
, ( 'PO2' , 'S01' , 100 )
, ( 'PO3' , 'S01' , 100 )
)
SELECT p.order_no
, s.batch_no
, MIN( p.sum_order - s.sum_stock + s.quantity
, s.sum_stock - p.sum_order + p.quantity
) AS quantity
FROM (SELECT p.*
, SUM(quantity)
OVER( PARTITION BY part_no
ORDER BY order_no ) sum_order
FROM purchase_order p
) p
LEFT OUTER JOIN
(SELECT s.*
, SUM(quantity)
OVER( PARTITION BY part_no
ORDER BY batch_no ) sum_stock
FROM stock s
) s
ON s.part_no = p.part_no
AND p.sum_order > s.sum_stock - s.quantity
AND s.sum_stock > p.sum_order - p.quantity
ORDER BY
p.order_no
, s.batch_no
;
------------------------------------------------------------------------------

ORDER_NO BATCH_NO QUANTITY
-------- -------- -----------
PO1 B1 20
PO2 B1 80
PO2 B2 20
PO3 B2 30
PO3 B3 70

5 record(s) selected.

(Updated)
An example:

------------------------------ Commands Entered
------------------------------
WITH
stock(part_no , batch_no , quantity) AS (
VALUES
( 'S01' , 'B1' , 100 )
, ( 'S01' , 'B2' , 50 )
, ( 'S01' , 'B3' , 75 )
)
, purchase_order(order_no , part_no , quantity) AS (
VALUES
( 'PO1' , 'S01' , 20 )
, ( 'PO2' , 'S01' , 100 )
, ( 'PO3' , 'S01' , 100 )
)
SELECT p.order_no
, s.batch_no
, MIN( p.sum_order - s.sum_stock + s.quantity
, s.sum_stock - p.sum_order + p.quantity
) AS quantity
FROM (SELECT p.*
, SUM(quantity)
OVER( PARTITION BY part_no
ORDER BY order_no ) sum_order
FROM purchase_order p
) p
LEFT OUTER JOIN
(SELECT s.*
, SUM(quantity)
OVER( PARTITION BY part_no
ORDER BY batch_no ) sum_stock
FROM stock s
) s
ON s.part_no = p.part_no
AND p.sum_order > s.sum_stock - s.quantity
AND s.sum_stock > p.sum_order - p.quantity
ORDER BY
p.order_no
, s.batch_no
;
------------------------------------------------------------------------------

ORDER_NO BATCH_NO QUANTITY
-------- -------- -----------
PO1 B1 20
PO2 B1 80
PO2 B2 20
PO3 B2 30
PO3 B3 70

5 record(s) selected.

A little longer, but the following repeated expressions calculated
onece.
s.sum_stock - s.quantity
p.sum_order - p.quantity

------------------------------ Commands Entered
------------------------------
WITH
stock(part_no , batch_no , quantity) AS (
VALUES
( 'S01' , 'B1' , 100 )
, ( 'S01' , 'B2' , 50 )
, ( 'S01' , 'B3' , 75 )
)
, purchase_order(order_no , part_no , quantity) AS (
VALUES
( 'PO1' , 'S01' , 20 )
, ( 'PO2' , 'S01' , 100 )
, ( 'PO3' , 'S01' , 100 )
)
SELECT p.order_no
, s.batch_no
, MIN( p.sum_order - s.lag_stock
, s.sum_stock - p.lag_order
) AS quantity
FROM (SELECT p.*
, p.sum_order - p.quantity AS lag_order
FROM (SELECT p.*
, SUM(quantity)
OVER( PARTITION BY part_no
ORDER BY order_no ) sum_order
FROM purchase_order p
) p
) p
LEFT OUTER JOIN
(SELECT s.*
, s.sum_stock - s.quantity AS lag_stock
FROM (SELECT s.*
, SUM(quantity)
OVER( PARTITION BY part_no
ORDER BY batch_no ) sum_stock
FROM stock s
) s
) s
ON s.part_no = p.part_no
AND p.sum_order > s.lag_stock
AND s.sum_stock > p.lag_order
ORDER BY
p.order_no
, s.batch_no
;
------------------------------------------------------------------------------

ORDER_NO BATCH_NO QUANTITY
-------- -------- -----------
PO1 B1 20
PO2 B1 80
PO2 B2 20
PO3 B2 30
PO3 B3 70

5 record(s) selected.

Google the code for a what Codd called a T-Join . If you cannort find
it, I will post stuff from SQL FOR SMARTIES chapter 25.6. It is long
for a forum.

--CELKO-- <jcelko212 (AT) earthlink (DOT) net> wrote in news:6c73b10a-4e56-4beb-a0ad-
0b65238dd2bc (AT) z12g2000yqz (DOT) googlegroups.com:

Codd's book "The Relational Model for Database Management Version 2" can be
found here:
http://www.slideshare.net/thealoysiu...-for-database-
management

The T-Join is described in chapter 5.7

Cheers!

--
Jeroen

On Oct 4, 12:05*am, TheBoss <TheB... (AT) invalid (DOT) nl> wrote:
Thanks a lot for all your help.
I've learned something new.

I wrote an article on T-Joins and knapsack problems in SQL for SQL
Server Central. I do not knwo when it will be posted.