I have 2 tables one is Warehouse with prodid and prodname then the Orders table where we have OrderNo and RequiredProdName
What is the requirement?
To identify the orders that could be honored and completed depending on the products that we have in the warehouse.
Let's take ord001, it should contain Prod007, Prod008 and Prod009 from the warehouse. If these products exists in the warehouse, then the order is fulfilled and this 3 products exists and so ord001 should appear in the final solution.
Let's take ord002, it should contain Prod008, Prod009 and Prod012 if they exists in the warehouse. Prod008 exists in the warehouse and even if one Prod008 was taken in the first order ord001 there is still 1 because we have 2 pieces in the warehouse but Prod009 in the warehouse is only one piece and already send it to order001 and Prod012 can be honored as we still have 1 piece in warehouse, this order002 should NOT appear in the output because although Prod008 and Prod012 can be honored for Prod009 we no longer have - there was just one product and that went to first order.
Let's take ord003, this order contains 2 products, Prod008 of which we still have 1 in inventory and Prod014, of which we still have 1 in inventory, so this order can be honored because we have these products, items in the warehouse.
Final output : ord001 and ord003
It's something recursive to look back to see if that product was allocated or not.
I have created the Dbfiddle https://dbfiddle.uk/3vYH2i6B with ddl and dml statements and i put there my attemp but i m far away from the correct output and i really don t know how to solve this task so please bear with me.
Also i will add the tables here: Orders
| OrderNo | RequiredProdName |
|---|---|
| ORD001 | Prod007 |
| ORD001 | Prod008 |
| ORD001 | Prod009 |
| ORD002 | Prod008 |
| ORD002 | Prod009 |
| ORD002 | Prod012 |
| ORD003 | Prod008 |
| ORD003 | Prod014 |
Warehouse
| ProdId | ProdName |
|---|---|
| 1 | Prod007 |
| 2 | Prod008 |
| 3 | Prod008 |
| 4 | Prod012 |
| 5 | Prod009 |
| 6 | Prod014 |
| 7 | Prod015 |
What i have tried:
WITH orders1 AS (
select orderno, requiredprodname, rn_order, stock, demand, stock-demand as remaining
from (
SELECT
o.orderno,
o.requiredprodname,
ROW_NUMBER() OVER(PARTITION BY o.orderno ORDER BY o.requiredprodname) AS rn_order,
stock,
count(o.requiredprodname) over(partition by o.orderno, o.requiredprodname) as demand
FROM orders o
left join (
select prodname, count(*) As stock
from warehouse
group by prodname
) w
on o.RequiredProdName = w.prodname
) a
),
cte as (
select orderno, requiredprodname, rn_order, stock, demand, remaining
from orders1
--where rn_order=1
UNION ALL
SELECT
o.orderno, o.requiredprodname, o.rn_order, o.stock, c.demand,
c.remaining + c.stock - o.demand AS remaining
FROM orders1 o
INNER JOIN CTE c
ON c.requiredprodname != o.requiredprodname
AND c.rn_order = o.rn_order - 1
)
SELECT *
from cte;
After taking a very deep dive into this problem, my conclusion is:
Just say no. Instead look into developing an iterative procedural solution that considers and processes one order at a time.
If you really want to see what I came up with, here is the db<>fiddle. I've added quite a few comments, but I expect it would be a challenge for anyone other than a senior, experienced SQL programmer to understand, and even that is a reach.
PLAN A:
My initial plan was to follow your lead and generate a recursive CTE with rows for each combination or order and product. Each CTE row would have dependencies on earlier rows.
Unfortunately, these CTE dependencies do not form a linear path. The updated remaining quantity for a product not only depends on the prior inventory for that product, but on the calculated fulfilment for that order, which in turn depends on all of the current product quantity requests, and all of the prior order remaining inventory quantities.
Ultimately this results in in a one-to-many CTE back-reference dependencies, which is not allowed in SQL server. Even is all back references refer to rows from previous recursion cycles, they are still disallowed.
PLAN B:
Since the only critical piece if information that must be calculated in sequence is the fulfillment flag, I took another stab and a recursive CTE that only results in one row per order and only records the fulfillment flag. Subsequent order calculations could recalculate the remaining inventory from scratch, based on initial inventory, knowledge of earlier-fulfilled orders, and the products allocated to those prior fulfilled orders.
Unfortunately, the same recursive CTE limitation came into play. Each recursive CTE row calculation not only needs to access the prior row, but all prior rows to identify all prior fulfilled orders. Again this is not allowed in SQL server.
PLAN C:
The next approach was to generate a recursive CTE, one per order, that not only sets the fulfillment flag, but also maintains a complete inventory of remaining quantities. A comma separated list was selected to represent the inventory. (XML or JSON might have been workable alternatives.)
Each CTE iteration would unpack the inventory, perform apply the order logic, update the inventory, and pack the results back into a comma-separated list.
Fitting this all into the CTE recursion query involved several nested queries, multiple CROSS APPLYs, and logic to pull the results back together.
Again SQL Server recursive CTE limitations kicked in. Recursive CTEs do not allow OUTER JOINs. They do not allow GROUP BY. They do not allow aggregate functions like STRING_JOIN(). I even tried a DISTINCT in one lame attempt - not allowed.
I was finally able to fall back on the old FOR XML string aggregation technique and together with a few other klugy techniques, was able to get a working CTE.
The results are not anything that I would ever offer for production use for reasons summarized at the top of this post.
I only post this as a lesson that sometimes going to extreme measures to get a job done using the wrong tools yields a barely workable and completely unmaintainable result, and that the better solution is to take a step back and reevaluate the task and look for a better tool set.