Reports the AI builds with you, from a conversation

SELECT lg.name AS location_group, l.name AS location, lt.name AS location_type, COUNT(DISTINCT t.partId) AS part_count, SUM(t.qty) AS qty_on_hand, ROUND(SUM(t.qty * COALESCE(pc.avgCost, 0)), 2) AS inventory_value
FROM tag AS t
JOIN location AS l ON l.id = t.locationId
JOIN locationgroup AS lg ON lg.id = l.locationGroupId
JOIN locationtype AS lt ON lt.id = l.typeId
JOIN part AS p ON p.id = t.partId
LEFT JOIN partcost AS pc ON pc.partId = t.partId
WHERE t.typeId IN (30, 40) AND t.qty <> 0
GROUP BY lg.name, l.name, lt.name
ORDER BY lg.name, inventory_value DESC
LIMIT 201

SELECT so.id AS soId, so.num AS soNum, so.customerPO, so.priorityId, s.id AS shipId, s.num AS shipNum, s.statusId, c.name AS carrier, cs.name AS service, s.cartonCount, CAST(s.dateShipped as CHAR) AS dateShipped, CAST(s.dateCreated as CHAR) AS shipCreated, sc.cartonNum, sc.trackingNum, sc.freightWeight, sc.weightUOM
FROM so
LEFT JOIN ship AS s ON s.soId = so.id AND s.orderTypeId = 20
LEFT JOIN shipcarton AS sc ON sc.shipId = s.id
LEFT JOIN carrier AS c ON c.id = s.carrierId
LEFT JOIN carrierservice AS cs ON cs.id = s.carrierServiceId
WHERE so.num = @soNum
LIMIT 101

SELECT r.id, r.name, r.path, m.name AS module
FROM report AS r
JOIN reporttomodule AS rm ON rm.reportId = r.id
JOIN module AS m ON m.id = rm.moduleId
WHERE m.name IN ('Shipping', 'Ship') OR r.name LIKE '%Label%'
ORDER BY m.name, r.name
LIMIT 101

SELECT id, name
FROM priority
ORDER BY id
LIMIT 101

SELECT id, name
FROM addresstype
ORDER BY id
LIMIT 101

SELECT co.name AS FROMNAME, COALESCE(s.dateShipped, NOW()) AS SHIPDATE, sc.freightWeight AS WEIGHT, COALESCE(NULLIF(sc.weightUOM, ''), 'lb') AS WEIGHTUOM, UPPER(COALESCE(cs.name, car.name)) AS SERVICE, sc.trackingNum AS TRACKINGNUM, so.num AS SONUM, so.customerPO AS PONUM, CONCAT(sc.cartonNum, ' of ', (SELECT COUNT(*)
FROM shipcarton AS x
WHERE x.shipId = s.id)) AS CARTONLABEL, so.priorityId AS PRIORITYID
FROM ship AS s
JOIN shipcarton AS sc ON sc.shipId = s.id
LEFT JOIN so ON so.id = s.soId AND s.orderTypeId = 20
LEFT JOIN carrier AS car ON car.id = s.carrierId
LEFT JOIN carrierservice AS cs ON cs.id = s.carrierServiceId
LEFT JOIN company AS co ON co.defaultFlag = 1
WHERE s.id = @shipId
ORDER BY sc.cartonNum
LIMIT 101

SELECT so.statusId, COUNT(*) AS orders, MIN(DATE_FORMAT(so.dateIssued, '%Y-%m-%d')) AS first_issued, MAX(DATE_FORMAT(so.dateIssued, '%Y-%m-%d')) AS last_issued, SUM(so.subTotal) AS subtotal
FROM so
GROUP BY so.statusId
ORDER BY so.statusId
LIMIT 101

SELECT si.typeId, COUNT(*) AS line_count, SUM(si.totalPrice) AS total, COUNT(si.productId) AS with_product
FROM soitem AS si
GROUP BY si.typeId
ORDER BY si.typeId
LIMIT 101

SELECT DATE_FORMAT(so.dateIssued, '%Y-%m') AS month_key, COUNT(DISTINCT so.id) AS order_count, SUM(si.totalPrice) AS revenue
FROM so
JOIN soitem AS si ON si.soId = so.id
WHERE so.statusId IN (20, 25, 60, 70, 95) AND si.typeId IN (10, 11, 12, 20, 21, 30, 31) AND so.dateIssued >= DATE_SUB(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL @months - 1 MONTH) AND so.dateIssued < DATE_ADD(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 1 MONTH)
GROUP BY month_key
ORDER BY month_key
LIMIT 101

SELECT p.num AS product_num, p.description AS product_desc, SUM(si.qtyOrdered) AS qty, SUM(si.totalPrice) AS revenue, SUM(si.totalPrice) / SUM(SUM(si.totalPrice)) OVER () AS share
FROM so
JOIN soitem AS si ON si.soId = so.id
JOIN product AS p ON p.id = si.productId
WHERE so.statusId IN (20, 25, 60, 70, 95) AND si.typeId IN (10, 11, 12, 20, 21) AND so.dateIssued >= DATE_SUB(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL @months - 1 MONTH) AND so.dateIssued < DATE_ADD(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 1 MONTH)
GROUP BY p.id, p.num, p.description
ORDER BY revenue DESC
LIMIT 10

SELECT c.name AS customer_name, COUNT(DISTINCT so.id) AS order_count, SUM(si.totalPrice) AS revenue, SUM(si.totalPrice) / SUM(SUM(si.totalPrice)) OVER () AS share
FROM so
JOIN soitem AS si ON si.soId = so.id
JOIN customer AS c ON c.id = so.customerId
WHERE so.statusId IN (20, 25, 60, 70, 95) AND si.typeId IN (10, 11, 12, 20, 21, 30, 31) AND so.dateIssued >= DATE_SUB(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL @months - 1 MONTH) AND so.dateIssued < DATE_ADD(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 1 MONTH)
GROUP BY c.id, c.name
ORDER BY revenue DESC
LIMIT 10

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

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

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

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

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