“can you give me a list of ingredients that aren't really moving, and save it so i can pull it up each week?”

Every step the AI took

1. 1
   Checked the saved queries
   0 saved queries
2. 2
   Find ingredients (raw materials) that are not moving much, to build a weekly slow-mover list

   ✓ passed read-only checkrow cap applied

   Show the SQL

   SELECT pt.name AS part_type, COUNT(*) AS parts, SUM(p.activeFlag) AS active
   FROM part AS p
   JOIN parttype AS pt ON pt.id = p.typeId
   GROUP BY pt.name
   LIMIT 101

   4 rows · 245 ms
3. 3
   Find ingredients (raw materials) that are not moving much, to build a weekly slow-mover list

   List active inventory parts with BOM usage to identify which are ingredients

   ✓ passed read-only checkrow cap applied

   Show the SQL

   SELECT p.num, p.description, (SELECT COUNT(*)
   FROM bomitem AS bi
   WHERE bi.partId = p.id AND bi.typeId = 20) AS used_as_raw, (SELECT COUNT(*)
   FROM bomitem AS bi
   WHERE bi.partId = p.id AND bi.typeId = 10) AS made_as_fg
   FROM part AS p
   WHERE p.typeId = 10 AND p.activeFlag = 1
   ORDER BY p.num
   LIMIT 101

   64 rows · 258 ms
4. 4
   Looked up a table · inventorylog
   18 columns
5. 5
   Find ingredients (raw materials) that are not moving much, to build a weekly slow-mover list

   Profile inventory log event types and date range to define "movement"

   ✓ passed read-only checkrow cap applied

   Show the SQL

   SELECT t.id, t.name, COUNT(il.id) AS n, MIN(il.eventDate) AS first_dt, MAX(il.eventDate) AS last_dt, SUM(il.changeQty < 0) AS neg
   FROM inventorylog AS il
   LEFT JOIN inventorylogtype AS t ON t.id = il.typeId
   GROUP BY t.id, t.name
   ORDER BY n DESC
   LIMIT 101

   15 rows · 175 ms
6. 6
   Looked up a table · qty
7. 7
   Looked up a table · qtyonhand
   3 columns
8. 8
   Find ingredients (raw materials) that are not moving much, to build a weekly slow-mover list

   Full draft: ingredients with on-hand, value, 90-day usage, last used date, weeks of supply

   ✓ passed read-only checkrow cap applied

   Show the SQL

   SELECT p.num AS part, p.description, u.code AS uom, COALESCE(oh.qty, 0) AS on_hand, ROUND(COALESCE(oh.qty, 0) * COALESCE(pc.avgCost, 0), 2) AS on_hand_value, COALESCE(w.used, 0) AS used_in_window, DATE(lu.last_used) AS last_used, DATEDIFF(CURDATE(), lu.last_used) AS days_since_used, CASE WHEN COALESCE(w.used, 0) > 0 THEN ROUND(COALESCE(oh.qty, 0) / (w.used / (@days / 7.0)), 1) END AS weeks_of_supply
   FROM part AS p
   JOIN uom AS u ON u.id = p.uomId
   LEFT JOIN (SELECT PARTID, SUM(QTY) AS qty
   FROM qtyonhand
   GROUP BY PARTID) AS oh ON oh.PARTID = p.id
   LEFT JOIN partcost AS pc ON pc.partId = p.id
   LEFT JOIN (SELECT partId, -SUM(changeQty) AS used
   FROM inventorylog
   WHERE typeId IN (20, 40, 50) AND changeQty < 0 AND eventDate >= DATE_SUB(CURDATE(), INTERVAL @days DAY)
   GROUP BY partId) AS w ON w.partId = p.id
   LEFT JOIN (SELECT partId, MAX(eventDate) AS last_used
   FROM inventorylog
   WHERE typeId IN (20, 40, 50) AND changeQty < 0
   GROUP BY partId) AS lu ON lu.partId = p.id
   WHERE p.activeFlag = 1 AND p.typeId = 10 AND NOT EXISTS (SELECT 1
   FROM bomitem AS b
   WHERE b.partId = p.id AND b.typeId = 10) AND (EXISTS (SELECT 1
   FROM bomitem AS b
   WHERE b.partId = p.id AND b.typeId = 20) OR COALESCE(oh.qty, 0) > 0)
   ORDER BY (COALESCE(w.used, 0) > 0), weeks_of_supply DESC, on_hand_value DESC
   LIMIT 201

   44 rows · 292 ms
9. 9
   Find ingredients (raw materials) that are not moving much, to build a weekly slow-mover list

   Add flags/thresholds with defaults, exclude test parts and zero-stock items, filter to flagged only

   ✓ passed read-only checkrow cap applied

   Show the SQL

   SELECT *
   FROM (SELECT p.num AS part, p.description, u.code AS uom, COALESCE(oh.qty, 0) AS on_hand, ROUND(COALESCE(oh.qty, 0) * COALESCE(pc.avgCost, 0), 2) AS on_hand_value, COALESCE(w.used, 0) AS used_in_window, DATE(lu.last_used) AS last_used, DATEDIFF(CURDATE(), lu.last_used) AS days_since_used, CASE WHEN COALESCE(w.used, 0) > 0 THEN ROUND(COALESCE(oh.qty, 0) / (w.used / (COALESCE(@days, 90) / 7.0)), 1) END AS weeks_of_supply, CASE WHEN COALESCE(w.used, 0) = 0 THEN 'No usage in window' WHEN DATEDIFF(CURDATE(), lu.last_used) >= COALESCE(@idle_days, 30) THEN 'Idle' WHEN COALESCE(oh.qty, 0) / (w.used / (COALESCE(@days, 90) / 7.0)) >= COALESCE(@max_weeks, 52) THEN 'Overstocked' END AS flag
   FROM part AS p
   JOIN uom AS u ON u.id = p.uomId
   LEFT JOIN (SELECT PARTID, SUM(QTY) AS qty
   FROM qtyonhand
   GROUP BY PARTID) AS oh ON oh.PARTID = p.id
   LEFT JOIN partcost AS pc ON pc.partId = p.id
   LEFT JOIN (SELECT partId, -SUM(changeQty) AS used
   FROM inventorylog
   WHERE typeId IN (20, 40, 50) AND changeQty < 0 AND eventDate >= DATE_SUB(CURDATE(), INTERVAL COALESCE(@days, 90) DAY)
   GROUP BY partId) AS w ON w.partId = p.id
   LEFT JOIN (SELECT partId, MAX(eventDate) AS last_used
   FROM inventorylog
   WHERE typeId IN (20, 40, 50) AND changeQty < 0
   GROUP BY partId) AS lu ON lu.partId = p.id
   WHERE p.activeFlag = 1 AND p.typeId = 10 AND p.description NOT LIKE '%test%' AND NOT EXISTS (SELECT 1
   FROM bomitem AS b
   WHERE b.partId = p.id AND b.typeId = 10) AND (EXISTS (SELECT 1
   FROM bomitem AS b
   WHERE b.partId = p.id AND b.typeId = 20) OR COALESCE(oh.qty, 0) > 0)) AS x
   WHERE flag IS NOT NULL AND on_hand > 0
   ORDER BY FIELD(flag, 'No usage in window', 'Idle', 'Overstocked'), weeks_of_supply DESC, on_hand_value DESC
   LIMIT 201

   35 rows · 219 ms
10. 10
    Saved a reusable query · slow_moving_ingredients

    Show the query it prepared slow_moving_ingredients.sql

    The reusable query it saved — your team runs it with their own inputs, never the raw SQL.

    SELECT *
    FROM (
    SELECT p.num AS part, p.description, u.code AS uom,
      COALESCE(oh.qty,0) AS on_hand,
      ROUND(COALESCE(oh.qty,0) * COALESCE(pc.avgCost,0),2) AS on_hand_value,
      COALESCE(w.used,0) AS used_in_window,
      DATE(lu.last_used) AS last_used,
      DATEDIFF(CURDATE(), lu.last_used) AS days_since_used,
      CASE WHEN COALESCE(w.used,0) > 0 THEN ROUND(COALESCE(oh.qty,0) / (w.used / (COALESCE(@days,90)/7.0)),1) END AS weeks_of_supply,
      CASE
        WHEN COALESCE(w.used,0) = 0 THEN 'No usage in window'
        WHEN DATEDIFF(CURDATE(), lu.last_used) >= COALESCE(@idle_days,30) THEN 'Idle'
        WHEN COALESCE(oh.qty,0) / (w.used / (COALESCE(@days,90)/7.0)) >= COALESCE(@max_weeks,52) THEN 'Overstocked'
      END AS flag
    FROM part p
    JOIN uom u ON u.id = p.uomId
    LEFT JOIN (SELECT PARTID, SUM(QTY) qty
    FROM qtyonhand
    GROUP BY PARTID) oh ON oh.PARTID = p.id
    LEFT JOIN partcost pc ON pc.partId = p.id
    LEFT JOIN (SELECT partId, -SUM(changeQty) used
    FROM inventorylog
    WHERE typeId IN (20,40,50) AND changeQty < 0 AND eventDate >= DATE_SUB(CURDATE(), INTERVAL COALESCE(@days,90) DAY)
    GROUP BY partId) w ON w.partId = p.id
    LEFT JOIN (SELECT partId, MAX(eventDate) last_used
    FROM inventorylog
    WHERE typeId IN (20,40,50) AND changeQty < 0
    GROUP BY partId) lu ON lu.partId = p.id
    WHERE p.activeFlag = 1 AND p.typeId = 10
      AND p.description NOT LIKE '%test%'
      AND NOT EXISTS (SELECT 1
    FROM bomitem b
    WHERE b.partId = p.id AND b.typeId = 10)
      AND (EXISTS (SELECT 1
    FROM bomitem b
    WHERE b.partId = p.id AND b.typeId = 20) OR COALESCE(oh.qty,0) > 0)
    ) x
    WHERE flag IS NOT NULL AND on_hand > 0
    ORDER BY FIELD(flag,'No usage in window','Idle','Overstocked'), weeks_of_supply DESC, on_hand_value DESC

    saved as 'slow_moving_ingredients' — reusable, runs with your inputs