Sales Growth Opportunities — August 2026

SELECT TO_CHAR(t.trandate,'YYYY-MM') AS mth, ROUND(SUM(ABS(tl.netamount)),0) AS revenue,
       COUNT(DISTINCT t.id) AS orders
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id AND tl.mainline = 'F' AND tl.taxline = 'F'
WHERE t.type IN ('CustInvc','CashSale') AND tl.subsidiary <> 4
GROUP BY TO_CHAR(t.trandate,'YYYY-MM') ORDER BY TO_CHAR(t.trandate,'YYYY-MM')

-- MTD pacing (apples-to-apples, day 1-15)
SELECT TO_CHAR(t.trandate,'YYYY') AS yr, ROUND(SUM(ABS(tl.netamount)),2) AS mtd_rev, COUNT(DISTINCT t.id) AS orders
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id AND tl.mainline = 'F' AND tl.taxline = 'F'
WHERE t.type IN ('CustInvc','CashSale') AND tl.subsidiary <> 4
  AND TO_CHAR(t.trandate,'MM') = '08' AND TO_NUMBER(TO_CHAR(t.trandate,'DD')) <= 15
GROUP BY TO_CHAR(t.trandate,'YYYY') ORDER BY TO_CHAR(t.trandate,'YYYY')

SELECT TO_CHAR(t.trandate,'YYYY-MM') AS mth, NVL(tl.class, 0) AS class_id, ROUND(SUM(ABS(tl.netamount)),0) AS revenue
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id AND tl.mainline = 'F' AND tl.taxline = 'F'
WHERE t.type IN ('CustInvc','CashSale') AND tl.subsidiary <> 4
  AND t.trandate >= TO_DATE('2025-08-01','YYYY-MM-DD')
GROUP BY TO_CHAR(t.trandate,'YYYY-MM'), NVL(tl.class, 0)
ORDER BY TO_CHAR(t.trandate,'YYYY-MM'), NVL(tl.class, 0)

SELECT t.status, COUNT(*) AS orders, ROUND(SUM(ABS(t.foreigntotal)),2) AS backlog_value, MIN(t.trandate) AS oldest
FROM transaction t
WHERE t.type = 'SalesOrd' AND t.status IN ('A','B','E','F')
GROUP BY t.status ORDER BY SUM(ABS(t.foreigntotal)) DESC
-- NS status letters: SalesOrd A=Pending Approval, B=Pending Fulfillment, E=Partially Fulfilled, F=Pending Billing

SELECT t.id, t.tranid, c.entityid AS customer, ROUND(ABS(t.foreigntotal),2) AS order_total, t.trandate
FROM transaction t JOIN customer c ON t.entity = c.id
WHERE t.type = 'SalesOrd' AND t.status = 'A'
ORDER BY ABS(t.foreigntotal) DESC

SELECT c.id, c.entityid, c.companyname, ROUND(SUM(ABS(tl.netamount)),2) AS prior_revenue, MAX(t.trandate) AS last_purchase
FROM transaction t
JOIN customer c ON t.entity = c.id
JOIN transactionline tl ON tl.transaction = t.id AND tl.mainline = 'F' AND tl.taxline = 'F'
WHERE t.type IN ('CustInvc','CashSale') AND tl.subsidiary <> 4
GROUP BY c.id, c.entityid, c.companyname
HAVING MAX(t.trandate) < TO_DATE('2026-02-15','YYYY-MM-DD')
ORDER BY SUM(ABS(tl.netamount)) DESC FETCH FIRST 15 ROWS ONLY

SELECT c.id, c.entityid,
  ROUND(SUM(CASE WHEN t.trandate >= TO_DATE('2025-08-16','YYYY-MM-DD') THEN ABS(tl.netamount) ELSE 0 END),0) AS ttm_rev,
  ROUND(SUM(CASE WHEN t.trandate < TO_DATE('2025-08-16','YYYY-MM-DD') THEN ABS(tl.netamount) ELSE 0 END),0) AS prior12_rev
FROM transaction t
JOIN customer c ON t.entity = c.id
JOIN transactionline tl ON tl.transaction = t.id AND tl.mainline = 'F' AND tl.taxline = 'F'
WHERE t.type IN ('CustInvc','CashSale') AND tl.subsidiary <> 4
  AND t.trandate >= TO_DATE('2024-08-16','YYYY-MM-DD')
GROUP BY c.id, c.entityid
ORDER BY SUM(CASE WHEN t.trandate >= TO_DATE('2025-08-16','YYYY-MM-DD') THEN ABS(tl.netamount) ELSE 0 END) DESC
FETCH FIRST 10 ROWS ONLY

SELECT COUNT(*) AS one_time_buyers, ROUND(SUM(rev),2) AS their_revenue
FROM (SELECT t.entity, COUNT(DISTINCT t.id) AS txns, SUM(ABS(tl.netamount)) AS rev
      FROM transaction t
      JOIN transactionline tl ON tl.transaction = t.id AND tl.mainline = 'F' AND tl.taxline = 'F'
      WHERE t.type IN ('CustInvc','CashSale') AND tl.subsidiary <> 4
      GROUP BY t.entity HAVING COUNT(DISTINCT t.id) = 1)

SELECT c.id, c.entityid, ROUND(SUM(ABS(tl.netamount)),2) AS first_order_value, MAX(t.trandate) AS purchase_date
FROM transaction t
JOIN customer c ON t.entity = c.id
JOIN transactionline tl ON tl.transaction = t.id AND tl.mainline = 'F' AND tl.taxline = 'F'
WHERE t.type IN ('CustInvc','CashSale') AND tl.subsidiary <> 4
  AND t.entity IN (SELECT entity FROM (SELECT t2.entity, COUNT(DISTINCT t2.id) AS txns
    FROM transaction t2 WHERE t2.type IN ('CustInvc','CashSale')
    GROUP BY t2.entity HAVING COUNT(DISTINCT t2.id) = 1))
GROUP BY c.id, c.entityid
ORDER BY SUM(ABS(tl.netamount)) DESC FETCH FIRST 10 ROWS ONLY

SELECT ia.itemid AS item_a, ib.itemid AS item_b, COUNT(DISTINCT a.transaction) AS times_together
FROM transactionline a
JOIN transactionline b ON a.transaction = b.transaction AND a.item < b.item
JOIN transaction t ON t.id = a.transaction
JOIN item ia ON ia.id = a.item JOIN item ib ON ib.id = b.item
WHERE t.type IN ('CustInvc','CashSale') AND t.trandate >= TO_DATE('2025-08-15','YYYY-MM-DD')
  AND a.mainline = 'F' AND a.taxline = 'F' AND b.mainline = 'F' AND b.taxline = 'F'
  AND a.item IS NOT NULL AND b.item IS NOT NULL
  AND ia.itemtype IN ('InvtPart','Assembly','Kit') AND ib.itemtype IN ('InvtPart','Assembly','Kit')
GROUP BY ia.itemid, ib.itemid
ORDER BY COUNT(DISTINCT a.transaction) DESC FETCH FIRST 10 ROWS ONLY

SELECT COUNT(*) AS slow_items, ROUND(SUM(onhand_value),2) AS tied_up_value
FROM (SELECT i.id, SUM(iil.onhandvaluemli) AS onhand_value
      FROM item i JOIN inventoryitemlocations iil ON iil.item = i.id
      WHERE i.itemtype = 'InvtPart' AND NVL(i.isinactive,'F') = 'F'
      GROUP BY i.id HAVING SUM(iil.quantityonhand) > 0) iv
WHERE NOT EXISTS (SELECT 1 FROM transactionline tl JOIN transaction t ON t.id = tl.transaction
  WHERE tl.item = iv.id AND tl.mainline = 'F' AND t.type IN ('CustInvc','CashSale')
    AND t.trandate >= TO_DATE('2026-02-15','YYYY-MM-DD'))
-- Top-items variant: same predicate, GROUP BY item, ORDER BY value DESC