DAX需求：查找需发货的缺货（Backorder）及已完成订单

Hey there! Let's work through this order filtering problem step by step, and also cover how you can share your files if needed.

First, I noticed a potential contradiction in your conditions: you mentioned needing order_rows.aantal < 0 and order_rows.aantal > 0 at the same time. That can't be true for a single row in order_rows, so I’m guessing you mean you want orders that have at least one row with aantal < 0 and at least one row with aantal > 0 (along with the other requirements). If that's not right, just adjust the logic to match your actual needs!

Option 1: Orders with both positive and negative aantal rows #

This query groups orders and checks for the presence of both row types:

SELECT o.order_number
FROM orders o
-- Join to order_rows (adjust foreign key if your schema uses a different name)
JOIN order_rows ord ON o.order_id = ord.order_id
-- Join to stock (update the join condition to match your table structure)
JOIN stock s ON ord.stock_id = s.stock_id
WHERE 
  o.status = 'backorder'
  AND ord.returned = 'YES'
  AND s.location IN ('Backorder', 'Sent')
GROUP BY o.order_number
HAVING
  -- Ensure at least one row hasantal < 0
  SUM(CASE WHEN ord.aantal < 0 THEN 1 ELSE 0 END) > 0
  -- Ensure at least one row hasantal > 0
  AND SUM(CASE WHEN ord.aantal > 0 THEN 1 ELSE 0 END) > 0;

Option 2: If you made a typo (e.g., aantal != 0) #

If you actually need rows where aantal isn’t zero (instead of both positive and negative), use this simpler version:

SELECT DISTINCT o.order_number
FROM orders o
JOIN order_rows ord ON o.order_id = ord.order_id
JOIN stock s ON ord.stock_id = s.stock_id
WHERE 
  o.status = 'backorder'
  AND ord.returned = 'YES'
  AND s.location IN ('Backorder', 'Sent')
  AND ord.aantal != 0; -- Swap this for your actual aantal condition if needed

Stack Overflow doesn’t support direct upload of these file types, but you have a few options:

• Share a sample data snippet: Copy a small, anonymized subset of your tables (5-10 rows works) and format it as a Markdown table or code block. This helps others replicate your issue without needing the full file.
• Use a secure file share: If you must share the full file, use a service like Google Drive or Dropbox, make the link publicly accessible, and remove any sensitive data (like customer info) first. Just note that some users might avoid downloading external files, so sample data is usually better.

• Double-check your join conditions: Make sure the foreign keys (like order_id or stock_id) match your actual database schema—adjust them in the queries if needed.
• Test incrementally: Start by filtering just orders.status = 'backorder', then add the order_rows conditions, then join the stock table. This makes it easier to spot where the query might go wrong.

内容的提问来源于stack exchange，提问作者Ekfa