Sample Reports in SQL format

• Finance
  • Unpaid Sales Orders
  • Unpaid Purchase Orders
  • Cashflow
  • Inventory
  • Customers

Finance

Unpaid Sales Orders

• Basic query

  SELECT *
  FROM CODBEX_SALESORDER as SO
  JOIN
      CODBEX_SALESORDERSTATUS S ON SO.SALESORDER_SALESORDERSTATUS = S.SALESORDERSTATUS_ID
  WHERE 
      S.SALESORDERSTATUS_NAME != 'Paid'

• Count all sales orders and group by CUSTOMER and order by number of unpaid orders

  SELECT
      C.CUSTOMER_NAME,
      COUNT(SO.SALESORDER_ID) AS UNPAID_SALES_ORDERS,
      SUM(SO.SALESORDER_TOTAL) AS UNPAID_AMOUNT
  FROM 
      CODBEX_CUSTOMER C
  JOIN 
      CODBEX_SALESORDER SO ON C.CUSTOMER_ID = SO.SALESORDER_CUSTOMER
  JOIN 
      CODBEX_SALESORDERSTATUS S ON SO.SALESORDER_SALESORDERSTATUS = S.SALESORDERSTATUS_ID
  WHERE 
      S.SALESORDERSTATUS_NAME != 'Paid'
  GROUP BY 
      C.CUSTOMER_ID, C.CUSTOMER_NAME
  ORDER BY 
      UNPAID_SALES_ORDERS DESC;

• Count sales orders and group by CUSTOMER and order by number of unpaid amount

  SELECT
      C.CUSTOMER_NAME,
      COUNT(SO.SALESORDER_ID) AS UNPAID_SALES_ORDERS,
      SUM(SO.SALESORDER_TOTAL) AS UNPAID_AMOUNT
  FROM 
      CODBEX_CUSTOMER C
  JOIN 
      CODBEX_SALESORDER SO ON C.CUSTOMER_ID = SO.SALESORDER_CUSTOMER
  JOIN 
      CODBEX_SALESORDERSTATUS S ON SO.SALESORDER_SALESORDERSTATUS = S.SALESORDERSTATUS_ID
  WHERE 
      S.SALESORDERSTATUS_NAME != 'Paid'
  GROUP BY 
      C.CUSTOMER_ID, C.CUSTOMER_NAME
  ORDER BY 
      UNPAID_AMOUNT DESC;

• All unpaid sales orders group by CUSTOMER, SALESORDER_NUMBER and order by SALESORDER_DUE ASC

  SELECT
      SO.SALESORDER_NUMBER,
      C.CUSTOMER_NAME,
      SO.SALESORDER_DATE,
      SO.SALESORDER_DUE,
      SUM(SO.SALESORDER_TOTAL) AS UNPAID_AMOUNT
  FROM 
      CODBEX_CUSTOMER C
  JOIN 
      CODBEX_SALESORDER SO ON C.CUSTOMER_ID = SO.SALESORDER_CUSTOMER
  JOIN 
      CODBEX_SALESORDERSTATUS S ON SO.SALESORDER_SALESORDERSTATUS = S.SALESORDERSTATUS_ID
  WHERE 
      S.SALESORDERSTATUS_NAME != 'Paid'
  GROUP BY 
      C.CUSTOMER_NAME, SO.SALESORDER_NUMBER
  ORDER BY SO.SALESORDER_DUE ASC

Unpaid Purchase Orders

• Basic query

  SELECT *
  FROM CODBEX_PURCHASEORDER as SO
  JOIN
      CODBEX_PURCHASEORDERSTATUS S ON SO.PURCHASEORDER_PURCHASEORDERSTATUS = S.PURCHASEORDERSTATUS_ID
  WHERE 
      S.PURCHASEORDERSTATUS_NAME != 'Paid'

• Count all purchase orders and group by SUPPLIER and order by number of unpaid orders

  SELECT
      C.SUPPLIER_NAME,
      COUNT(SO.PURCHASEORDER_ID) AS UNPAID_PURCHASE_ORDERS,
      SUM(SO.PURCHASEORDER_TOTAL) AS UNPAID_AMOUNT
  FROM 
      CODBEX_SUPPLIER C
  JOIN 
      CODBEX_PURCHASEORDER SO ON C.SUPPLIER_ID = SO.PURCHASEORDER_SUPPLIER
  JOIN 
      CODBEX_PURCHASEORDERSTATUS S ON SO.PURCHASEORDER_PURCHASEORDERSTATUS = S.PURCHASEORDERSTATUS_ID
  WHERE 
      S.PURCHASEORDERSTATUS_NAME != 'Paid'
  GROUP BY 
      C.SUPPLIER_ID, C.SUPPLIER_NAME
  ORDER BY 
      UNPAID_PURCHASE_ORDERS DESC;

• Count purchase orders and group by SUPPLIER and order by number of unpaid amount

  SELECT
      C.SUPPLIER_NAME,
      COUNT(SO.PURCHASEORDER_ID) AS UNPAID_PURCHASE_ORDERS,
      SUM(SO.PURCHASEORDER_TOTAL) AS UNPAID_AMOUNT
  FROM 
      CODBEX_SUPPLIER C
  JOIN 
      CODBEX_PURCHASEORDER SO ON C.SUPPLIER_ID = SO.PURCHASEORDER_SUPPLIER
  JOIN 
      CODBEX_PURCHASEORDERSTATUS S ON SO.PURCHASEORDER_PURCHASEORDERSTATUS = S.PURCHASEORDERSTATUS_ID
  WHERE 
      S.PURCHASEORDERSTATUS_NAME != 'Paid'
  GROUP BY 
      C.SUPPLIER_ID, C.SUPPLIER_NAME
  ORDER BY 
      UNPAID_AMOUNT DESC;

• All unpaid purchase orders group by SUPPLIER, PURCHASEORDER_NUMBER and order by PURCHASEORDER_DUE ASC

  SELECT
      SO.PURCHASEORDER_NUMBER,
      C.SUPPLIER_NAME,
      SO.PURCHASEORDER_DATE,
      SO.PURCHASEORDER_DUE,
      SUM(SO.PURCHASEORDER_TOTAL) AS UNPAID_AMOUNT
  FROM 
      CODBEX_SUPPLIER C
  JOIN 
      CODBEX_PURCHASEORDER SO ON C.SUPPLIER_ID = SO.PURCHASEORDER_SUPPLIER
  JOIN 
      CODBEX_PURCHASEORDERSTATUS S ON SO.PURCHASEORDER_PURCHASEORDERSTATUS = S.PURCHASEORDERSTATUS_ID
  WHERE 
      S.PURCHASEORDERSTATUS_NAME != 'Paid'
  GROUP BY 
      C.SUPPLIER_NAME, SO.PURCHASEORDER_NUMBER
  ORDER BY SO.PURCHASEORDER_DUE ASC

Cashflow

• Net Cashflow

  SELECT 
      SUM(TRANSACTION_AMOUNT) AS CASHFLOW_NET,
      DATE_TRUNC('day', TRANSACTION_DATE) AS CASHFLOW_DATE
  FROM (
      SELECT 
          SALESINVOICE_DATE AS TRANSACTION_DATE,
          SALESINVOICE_NET AS TRANSACTION_AMOUNT
      FROM CODBEX_SALESINVOICE
      UNION ALL
      SELECT 
          PURCHASEINVOICE_DATE AS TRANSACTION_DATE,
          -PURCHASEINVOICE_NET AS TRANSACTION_AMOUNT
      FROM CODBEX_PURCHASEINVOICE
  ) AS CombinedData
  GROUP BY DATE_TRUNC('day', TRANSACTION_DATE)
  ORDER BY CASHFLOW_DATE DESC;

• VAT each month(sum(sales_invoices.vat) - sum(purchase_invoices.vat))

  SELECT 
      SUM(VAT) AS CASHFLOW_VAT,
      DATE_TRUNC('month', TRANSACTION_DATE) AS CASHFLOW_DATE
  FROM (
      SELECT 
          SALESINVOICE_DATE AS TRANSACTION_DATE,
          SALESINVOICE_VAT AS VAT
      FROM CODBEX_SALESINVOICE
      UNION ALL
      SELECT 
          PURCHASEINVOICE_DATE AS TRANSACTION_DATE,
          -PURCHASEINVOICE_VAT AS VAT
      FROM CODBEX_PURCHASEINVOICE
  ) AS CombinedData
  GROUP BY DATE_TRUNC('month', TRANSACTION_DATE)
  ORDER BY CASHFLOW_DATE DESC;

Inventory

• Quantity left in inventory from STOCK_RECORD

  SELECT 
      p.PRODUCT_NAME,
      SUM(s.STOCKRECORD_DIRECTION) AS sum_direction
  FROM 
      CODBEX_STOCKRECORD s
  JOIN 
      CODBEX_PRODUCT p ON s.STOCKRECORD_PRODUCT = p.PRODUCT_ID
  GROUP BY 
      p.PRODUCT_NAME;

• Quantity left in inventory with STOCKADJUSTMENT and STOCKADJUSTMENTITEM

  SELECT 
      p.PRODUCT_NAME,
      SUM(s.STOCKRECORD_DIRECTION) +
      COALESCE((
          SELECT SUM(si.STOCKADJUSTMENTITEM_ADJUSTEDQUANTITY)
          FROM CODBEX_STOCKADJUSTMENTITEM si
          JOIN CODBEX_STOCKADJUSTMENT sa ON si.STOCKADJUSTMENTITEM_STOCKADJUSTMENT = sa.STOCKADJUSTMENT_ID
          WHERE si.STOCKADJUSTMENTITEM_PRODUCT = p.PRODUCT_ID
      ), 0) AS QUANTITY_LEFT
  FROM 
      CODBEX_PRODUCT p
  LEFT JOIN 
      CODBEX_STOCKRECORD s ON s.STOCKRECORD_PRODUCT = p.PRODUCT_ID
  GROUP BY 
      p.PRODUCT_NAME;

• Products ranked by number of sales

  SELECT
      p.PRODUCT_NAME,
      COUNT(si.SALESINVOICEITEM_ID) AS order_count
  FROM
      CODBEX_PRODUCT p
  JOIN
      CODBEX_SALESINVOICEITEM si ON p.PRODUCT_ID = si.SALESINVOICEITEM_PRODUCT
  GROUP BY
      p.PRODUCT_ID,
      p.PRODUCT_NAME
  ORDER BY
      order_count DESC;

• Products availability ordered by number of sales

  SELECT
      p.PRODUCT_NAME,
      COALESCE(SUM(si.SALESINVOICEITEM_QUANTITY), 0) AS total_sold,
      SUM(s.STOCKRECORD_DIRECTION) AS sum_direction
  FROM
      CODBEX_PRODUCT p
          LEFT JOIN
      CODBEX_SALESINVOICEITEM si ON p.PRODUCT_ID = si.SALESINVOICEITEM_PRODUCT
          LEFT JOIN
      CODBEX_STOCKRECORD s ON p.PRODUCT_ID = s.STOCKRECORD_PRODUCT
  GROUP BY
      p.PRODUCT_NAME
  ORDER BY
      total_sold DESC;

• Product categories ordered by number of sales

  SELECT
      pc.PRODUCTCATEGORY_NAME,
      COUNT(si.SALESINVOICEITEM_ID) AS sales_count
  FROM
      CODBEX_PRODUCTCATEGORY pc
  JOIN
      CODBEX_PRODUCT p ON pc.PRODUCTCATEGORY_ID = p.PRODUCT_CATEGORY
  JOIN
      CODBEX_SALESINVOICEITEM si ON p.PRODUCT_ID = si.SALESINVOICEITEM_PRODUCT
  GROUP BY
      pc.PRODUCTCATEGORY_NAME
  ORDER BY
      sales_count DESC;

• Manufacturers ordered by number of sales

  SELECT
  m.MANUFACTURER_NAME,
  COUNT(si.SALESINVOICEITEM_ID) AS sales_count
  FROM
  CODBEX_MANUFACTURER m
  LEFT JOIN
  CODBEX_PRODUCT p ON m.MANUFACTURER_ID = p.PRODUCT_MANUFACTURER
  LEFT JOIN
  CODBEX_SALESINVOICEITEM si ON p.PRODUCT_ID = si.SALESINVOICEITEM_PRODUCT
  GROUP BY
  m.MANUFACTURER_NAME
  ORDER BY
  sales_count DESC;

Customers

• Customers ranked by number of sales

  SELECT
      c.CUSTOMER_NAME,
      COUNT(si.SALESINVOICE_ID) AS invoice_count
  FROM
      CODBEX_CUSTOMER c
  LEFT JOIN
      CODBEX_SALESINVOICE si ON c.CUSTOMER_ID = si.SALESINVOICE_CUSTOMER
  GROUP BY
      c.CUSTOMER_ID,
      c.CUSTOMER_NAME
  ORDER BY
      invoice_count DESC;