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

> A real Claude session on a Fishbowl database (ILC's demo company, sample data), connected through ILC MCP for Fishbowl. The answer below is what the AI said; the queries are what it ran. Page: https://israellopezconsulting.com/fishbowl-mcp/questions/report-data-query

## Question

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

### Answer

The list is saved in Fishbowl as **`slow_moving_ingredients`**. Each week, just ask me to "run slow_moving_ingredients," or say something like "slow movers, last 60 days," and I'll run it with that window.

**Today's results (90-day usage window)**

Truly stalled:

| Part | Ingredient | On hand | Value | Last used | Flag |
|---|---|---|---|---|---|
| B203 | Chocolate Syrup | 2,379 oz | $787 | Jul 15 (110 days) | No usage in 90 days |
| B200 | Cocoa Powder | 4,552 oz | $1,584 | Sep 30 (33 days) | Idle |

Most overstocked (by weeks of supply at the current usage rate):

| Part | Ingredient | On hand | Value | Weeks of supply |
|---|---|---|---|---|
| BD301 | White Sugar | 16,787 lbs | $16,787 | ~1,340 |
| B202 | Baking Soda | 10,115 oz | $3,658 | ~719 |
| B204 | Salt | 7,523 oz | $2,757 | ~576 |
| BP203 | Butter | 10,950 oz | $8,369 | ~494 |
| H903 | Sliced Almonds | 3,498 oz | $1,242 | ~278 |
| H904 | Chopped Peanuts | 2,851 oz | $931 | ~270 |
| F103 | Cream of Tartar, Brand X | 2,102 oz | $694 | ~260 |
| T1002 | Lemon Cake mix | 1,696 lbs | $2,968 | ~202 |

There are 25 more items over a year of supply. The biggest dollar amounts sitting still are Vanilla Extract ($12.5k, ~130 weeks) and Chocolate Chips ($4.5k, ~59 weeks).

**How it decides**
- **Ingredient:** any active inventory part that isn't a finished good on a BOM, has stock on hand, and isn't a test part.
- **Movement:** shipments plus consumption by work orders and BOMs. Adjustments and scrap don't count as movement.
- **Flags:**
  - *No usage* means nothing used in the window.
  - *Idle* means no usage in 30+ days.
  - *Overstocked* means 52+ weeks of supply.
- **Adjustable:** you can change all three thresholds per run.

Nearly every ingredient clears the 52-week bar right now, so the "Overstocked" section is long. If you want a tighter list, I can raise the default to 104 weeks or so.

Want me to set this up as a scheduled task that runs every Monday morning and sends you the list?

## Follow-up

that's perfect - don't need it scheduled for now, i'll just pull it up when i need it. thanks!

### Answer

Sounds good. Just ask for `slow_moving_ingredients` whenever you want it, and I'll run it fresh.

## Behind the scenes

Every step the AI took, as recorded in the query log. All queries are read-only and row-capped.

1. Checked the saved queries — 0 saved queries
2. Ran a read-only query: Find ingredients (raw materials) that are not moving much, to build a weekly slow-mover list — 4 rows, 245 ms

   ```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
   ```

3. Ran a read-only query: Find ingredients (raw materials) that are not moving much, to build a weekly slow-mover list — 64 rows, 258 ms

   ```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
   ```

4. Looked up a table (inventorylog) — 18 columns
5. Ran a read-only query: Find ingredients (raw materials) that are not moving much, to build a weekly slow-mover list — 15 rows, 175 ms

   ```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
   ```

6. Looked up a table (qty)
7. Looked up a table (qtyonhand) — 3 columns
8. Ran a read-only query: Find ingredients (raw materials) that are not moving much, to build a weekly slow-mover list — 44 rows, 292 ms

   ```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
   ```

9. Ran a read-only query: Find ingredients (raw materials) that are not moving much, to build a weekly slow-mover list — 35 rows, 219 ms

   ```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
   ```

10. Saved a reusable query (slow_moving_ingredients) — saved as 'slow_moving_ingredients' — reusable, runs with your inputs

Learn more: https://israellopezconsulting.com/fishbowl-mcp · More examples: https://israellopezconsulting.com/fishbowl-mcp/questions