Common Queries

4 min

Current stock on hand

SELECT
    p.name,
    p.category,
    ps.stock       AS units_on_hand,
    ps.sub_stock   AS subunits_on_hand,
    p.minimum_stock,
    p.optimum_stock
FROM product_stocks ps
JOIN products p ON p.id = ps.product_id
WHERE ps.deleted_at IS NULL
  AND p.deleted_at IS NULL
ORDER BY p.name;

Reorder list — below minimum stock

SELECT
    p.name,
    ps.stock       AS units_on_hand,
    p.minimum_stock,
    p.optimum_stock,
    p.optimum_stock - ps.stock AS suggested_reorder_qty
FROM product_stocks ps
JOIN products p ON p.id = ps.product_id
WHERE ps.deleted_at IS NULL
  AND p.deleted_at IS NULL
  AND ps.stock < p.minimum_stock
ORDER BY (p.minimum_stock - ps.stock) DESC;

Products nearing expiry (via sold service package products)

This only surfaces expiry data for products consumed as part of a bundled service, since batch-level expiry on directly-sold products isn't exposed (see Stock & Services):

SELECT
    sspp.name,
    sspp.batch_no,
    sspp.expiration_date,
    sspp.quantity
FROM sold_service_package_products sspp
WHERE sspp.deleted_at IS NULL
  AND sspp.expiration_date IS NOT NULL
  AND sspp.expiration_date <= DATEADD(day, 30, CURRENT_DATE)
ORDER BY sspp.expiration_date;

Service package revenue

SELECT
    ap.name                                    AS service_name,
    COUNT(*)                                   AS times_sold,
    CAST(SUM(ssp.total) AS NUMERIC(18,2))      AS total_revenue
FROM sold_service_packages ssp
JOIN service_packages sp   ON sp.id = ssp.service_package_id
JOIN available_services ap ON ap.id = sp.service_id
WHERE ssp.deleted_at IS NULL
GROUP BY ap.name
ORDER BY total_revenue DESC;