We use the same tables and same data as part 1, so read part 1 for the setup.
When combining the FIFO for multiple orders with the route calculation, we get this nice piece of sql:
with orderlines as ( select o.ordno , o.item , o.qty , nvl(sum(o.qty) over ( partition by o.item order by o.ordno rows between unbounded preceding and 1 preceding ),0) + 1 from_qty , nvl(sum(o.qty) over ( partition by o.item order by o.ordno rows between unbounded preceding and current row ),0) to_qty from orderline o where ordno in (51, 62, 73) ), orderbatch as ( select o.item , sum(o.qty) qty from orderlines o group by o.item ), fifo as ( select s.loc , s.item , s.loc_qty , least(s.loc_qty, s.ord_qty - s.sum_prv_qty) pick_qty , sum_prv_qty + 1 from_qty , least(sum_qty, ord_qty) to_qty from ( select o.item , o.qty ord_qty , i.loc , i.qty loc_qty , nvl(sum(i.qty) over ( partition by i.item order by i.purch, i.loc rows between unbounded preceding and 1 preceding ),0) sum_prv_qty , nvl(sum(i.qty) over ( partition by i.item order by i.purch, i.loc rows between unbounded preceding and current row ),0) sum_qty from orderbatch o join inventory i on i.item = o.item ) s where s.sum_prv_qty < s.ord_qty ), pick as ( select to_number(substr(f.loc,1,1)) warehouse , substr(f.loc,3,1) aisle , dense_rank() over ( order by to_number(substr(f.loc,1,1)), -- warehouse substr(f.loc,3,1) -- aisle ) aisle_no , to_number(substr(f.loc,5,2)) position , f.loc , f.item , f.pick_qty pick_at_loc , o.ordno , least( f.loc_qty , least(o.to_qty, f.to_qty) - greatest(o.from_qty, f.from_qty) + 1 ) qty_for_ord from fifo f join orderlines o on o.item = f.item and o.to_qty >= f.from_qty and o.from_qty <= f.to_qty ) select p.loc , p.item , p.pick_at_loc , p.ordno , p.qty_for_ord from pick p order by p.warehouse , p.aisle_no ,case when mod(p.aisle_no,2) = 1 then p.position else -p.position end /
That gives us this output:
LOC ITEM PICK_AT_LOC ORDNO QTY_FOR_ORD ---------- ---------- ----------- ---------- ----------- 1-A-02 B1 5 73 5 1-A-20 A1 18 51 18 1-B-15 B1 2 51 2 1-B-11 B1 4 51 3 1-B-11 B1 4 73 1 1-C-04 B1 12 51 12 1-C-05 A1 6 73 6 2-A-02 A1 24 51 6 2-A-02 A1 24 73 10 2-A-02 A1 24 62 8 2-D-23 B1 1 51 1 11 rows selected.
So now the operator goes "up" in aisle 1-A, "down" in aisle 1-B and "up" again in aisle 1-C, and so on.
The ultimate picking query - picking multiple orders by FIFO in an efficient route through the warehouse. What more could you possibly want? :-D
You may feel that is a complex piece of SQL and it would be a lot easier to understand if it was written procedurally in PL/SQL, C# or Java with a nice flow of statements and IFs and THENs and LOOPs you can single-step debug in your head.
But let me tell you, that if you try to understand set based processing and how to really use the power of SQL to create queries like these - then your application stands a much better chance of performing superior to anything else. If you can gain manyfold performance increase by doing SQL properly, why bother nit-picking on whether an integer datatype shaves fractions of CPU time compared to a decimal datatype in your C# program?
A recent AskTom thread discusses a similar theme. I gave an example of how an application written as one SQL wrapped in a PL/SQL procedure survives whenever there is a new fancy GUI.
Use the power of SQL - you won't regret it :-D
And let me for the third and final time give you Monty's great quote:
"Don’t you just love these kind of challenges? It’s why we do what we do!"
If you wish, you can download the script for this demo.