Tailored for Your Industry

SELECT
  i.displayname                AS item,
  il.quantityonhand            AS qty_on_hand,
  il.quantityavailable         AS qty_available,
  ROUND(
    SUM(tl.quantity) /
    NULLIF(il.quantityonhand, 0)
  , 1)                         AS inventory_turns,
  ROUND(SYSDATE - MAX(t.trandate))
                               AS days_since_last_sale,
  CASE
    WHEN SYSDATE - MAX(t.trandate) > 180
      THEN 'Dead Stock'
    WHEN SYSDATE - MAX(t.trandate) > 90
      THEN 'Slow Moving'
    ELSE 'Active'
  END                          AS stock_status
FROM
  Item i
  LEFT JOIN InventoryBalance il
    ON i.id = il.item
  LEFT JOIN TransactionLine tl
    ON i.id = tl.item
  LEFT JOIN Transaction t
    ON tl.transaction = t.id
    AND t.type = 'CustInvc'
WHERE
  i.type = 'InvtPart'
GROUP BY
  i.displayname, il.quantityonhand,
  il.quantityavailable
ORDER BY inventory_turns ASC
FETCH FIRST 50 ROWS ONLY

SELECT
  TRUNC(t.trandate, 'MM')       AS month,
  COUNT(DISTINCT t.entity)      AS active_subscribers,
  SUM(tl.netamount)             AS mrr,
  SUM(tl.netamount) - LAG(SUM(tl.netamount))
    OVER (ORDER BY TRUNC(t.trandate, 'MM'))
                                AS mrr_change,
  ROUND(
    (SUM(tl.netamount) - LAG(SUM(tl.netamount))
      OVER (ORDER BY TRUNC(t.trandate, 'MM')))
    * 100.0 /
    NULLIF(LAG(SUM(tl.netamount))
      OVER (ORDER BY TRUNC(t.trandate, 'MM')), 0)
  , 1)                          AS growth_pct
FROM
  Transaction t
  INNER JOIN TransactionLine tl
    ON t.id = tl.transaction
  INNER JOIN Item i
    ON tl.item = i.id
WHERE
  t.type = 'CustInvc'
  AND t.posting = 'T'
  AND i.type = 'Service'
  AND t.trandate >= ADD_MONTHS(SYSDATE, -12)
GROUP BY TRUNC(t.trandate, 'MM')
ORDER BY month