SQL Queries for Custom Reports

These sql queries for sql-ledger can be used in phpPgAdmin or psql. You can use report generator to build fully functional reports which can be integrated well into sql-ledger.

When using with report generator, leave out the WHERE clause. If you do need to

Simple SQL Queries

Sales summary report

SELECT 
  ar.invnumber, 
  ar.transdate, 
  c.name AS customer, 
  ar.netamount, 
  ar.amount - ar.netamount AS tax, 
  ar.amount, 
  ar.paid, 
  ar.invoice
FROM ar
JOIN customer c ON (c.id = ar.customer_id)

Sales summary report with department and warehouse

SELECT 
  ar.invnumber, ar.transdate, 
  c.name AS customer, 
  ar.netamount, 
  ar.amount - ar.netamount AS tax, 
  ar.amount, 
  ar.paid, ar.invoice, 
  d.description AS department, 
  w.description AS warehouse
FROM ar
JOIN customer c ON (c.id = ar.customer_id)
JOIN department d ON (d.id = ar.department_id)
JOIN warehouse W ON (w.id = ar.warehouse_id)

Sales report with items

SELECT 
  ar.invnumber, 
  ar.transdate, 
  c.name AS customer
  p.partnumber, 
  ar.description, 
  i.qty, 
  i.sellprice, 
  i.qty * i.sellprice AS extended
FROM ar
JOIN customer c ON (c.id = ar.customer_id)
JOIN invoice i ON (i.id = ar.trans_id)

List of customers

SELECT 
  customernumber, 
  name,
  creditlimit
FROM customer
WHERE LOWER(name) LIKE '%bank%'
ORDER BY name;

Cash accounts with current balances

SELECT 
  accno, 
  description, 
  (SELECT SUM(amount) FROM acc_trans WHERE acc_trans.chart_id = chart.id) AS balance
FROM chart 
WHERE link LIKE '%_paid%'

Parts list

SELECT 
  p.partnumber,
  pg.partsgroup,
  p.description,
  p.lastcost,
  p.rop,
  p.rop * p.lastcost AS reorder_amount
FROM parts p
JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
WHERE inventory_accno_id IS NOT NULL
ORDER BY partnumber

Advanced SQL Queries

Inventory onhand on specific date

SELECT
  p.partnumber,
  p.description,
  pg.partsgroup,
  p.unit,

(SELECT SUM(0-i.qty) AS onhand
FROM invoice i
JOIN ap ON (ap.id = i.trans_id)
WHERE ap.transdate <= '01-01-08'
AND i.parts_id = p.id) 
  AS purchase,

(SELECT SUM(i.qty) AS onhand
FROM invoice i
JOIN ar ON (ar.id = i.trans_id)
WHERE ar.transdate <= '01-01-08'
AND i.parts_id = p.id) 
  AS sale

FROM parts p
LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)

Customer balances on a specific date

SELECT
  ct.id,
  ct.customernumber,
  ct.name,
  SUM(0 - ac.amount) AS balance

FROM customer ct
JOIN ar aa ON (ct.id = aa.customer_id)
JOIN acc_trans ac ON (aa.id = ac.trans_id)
JOIN chart c ON (c.id = ac.chart_id)

WHERE (ac.transdate <= '06-30-2007')
AND (c.link = 'AR')

GROUP BY 1,2,3

ORDER BY customernumber ASC

Sales summary by month

SELECT 
   TO_CHAR(transdate, 'YY-MM') AS month, 
   d.description AS department, 
   SUM(netamount)

FROM ar
JOIN department d ON (d.id = ar.department_id)
WHERE (transdate BETWEEN '01.07.2005' AND '30.06.2006')
GROUP BY TO_CHAR(transdate, 'YY-MM'), d.description

Sales Summary by group and month

SELECT 
  d.description AS department, 
  pg.partsgroup, 
  TO_CHAR(ar.transdate, 'YY-MM') AS month, 
  SUM(0 - i.qty * i.sellprice) AS amount

FROM invoice i
JOIN ar ON (ar.id = i.trans_id)
JOIN parts p ON (p.id = i.parts_id)
JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
JOIN department d ON (d.id = ar.department_id)

WHERE ar.transdate BETWEEN '01.07.2005' AND '30.06.2006'

GROUP BY 
  d.description, 
  pg.partsgroup, 
  TO_CHAR(ar.transdate, 'YY-MM')

ORDER BY 1, 2

Cash received today with age of AR in days

SELECT 
 c.accno, 
 c.description AS acc_title, 
 d.description AS department, 
 a.invnumber, ct.name, 
 ac.transdate - a.transdate AS days, 
 ac.source, 
 ac.amount, 
 e.name AS salesper, 
 a.notes, 
 ac.memo 

FROM ar a 
JOIN acc_trans ac ON (a.id = ac.trans_id) 
JOIN chart c ON (ac.chart_id = c.id) 
JOIN customer ct ON (a.customer_id = ct.id) 
JOIN employee e ON (a.employee_id = e.id)
LEFT JOIN department d ON (d.id = a.department_id) 

WHERE (ac.transdate = '30.05.06')
AND (c.link LIKE '%AR_paid%')
AND (a.department_id IN (SELECT id FROM department WHERE description IN ('LC','LS')))

ORDER BY days

Trial Balance with Month Headings

SELECT accno, 
 description,
 (SELECT SUM(amount) FROM acc_trans ac 
 WHERE ac.chart_id = chart.id 
 AND TO_CHAR(transdate, 'YY-MM') = '06-01') AS jan,
 (SELECT SUM(amount) FROM acc_trans ac 
 WHERE ac.chart_id = chart.id 
 AND TO_CHAR(transdate, 'YY-MM') = '06-02') AS fab,
 (SELECT SUM(amount) FROM acc_trans ac 
 WHERE ac.chart_id = chart.id 
 AND TO_CHAR(transdate, 'YY-MM') = '06-03') AS mar,
 (SELECT SUM(amount) FROM acc_trans ac 
 WHERE ac.chart_id = chart.id 
 AND TO_CHAR(transdate, 'YY-MM') = '06-04') AS apr,
 (SELECT SUM(amount) FROM acc_trans ac 
 WHERE ac.chart_id = chart.id 
 AND TO_CHAR(transdate, 'YY-MM') = '06-05') AS may,
 (SELECT SUM(amount) FROM acc_trans ac 
 WHERE ac.chart_id = chart.id 
 AND TO_CHAR(transdate, 'YY-MM') = '06-06') AS jun,
 (SELECT SUM(amount) FROM acc_trans ac 
 WHERE ac.chart_id = chart.id 
 AND TO_CHAR(transdate, 'YY-MM') = '05-07') AS jul,
 (SELECT SUM(amount) FROM acc_trans ac 
 WHERE ac.chart_id = chart.id 
 AND TO_CHAR(transdate, 'YY-MM') = '05-08') AS aug,
 (SELECT SUM(amount) FROM acc_trans ac 
 WHERE ac.chart_id = chart.id 
 AND TO_CHAR(transdate, 'YY-MM') = '05-09') AS sep,
 (SELECT SUM(amount) FROM acc_trans ac 
 WHERE ac.chart_id = chart.id 
 AND TO_CHAR(transdate, 'YY-MM') = '05-10') AS oct,
 (SELECT SUM(amount) FROM acc_trans ac 
 WHERE ac.chart_id = chart.id 
 AND TO_CHAR(transdate, 'YY-MM') = '05-11') AS nov,
 (SELECT SUM(amount) FROM acc_trans ac 
 WHERE ac.chart_id = chart.id 
 AND TO_CHAR(transdate, 'YY-MM') = '05-12') AS dec,
FROM chart
WHERE charttype = 'A'
ORDER BY accno

Troubleshooting database problems

Transactions without Departments

SELECT 'AR', id, invnumber AS reference, transdate
FROM ar
WHERE id NOT IN (SELECT DISTINCT trans_id FROM dpt_trans)

UNION ALL

SELECT 'AP', id, invnumber AS reference, transdate
FROM ap
WHERE id NOT IN (SELECT DISTINCT trans_id FROM dpt_trans)

UNION ALL

SELECT 'GL', id, reference, transdate
FROM gl
WHERE id NOT IN (SELECT DISTINCT trans_id FROM dpt_trans)

Unbalanced Journals

SELECT 'GL' AS mod, gl.reference, SUM(ac.amount)
FROM acc_trans ac
JOIN gl ON (gl.id = ac.trans_id)
GROUP BY 1, 2 
HAVING SUM(ac.amount) <> 0

UNION ALL

SELECT 'AR' AS mod, ar.invnumber, SUM(ac.amount)
FROM acc_trans ac
JOIN ar ON (ar.id = ac.trans_id)
GROUP BY 1, 2 
HAVING SUM(ac.amount) <> 0

UNION ALL

SELECT 'AP' AS mod, ap.invnumber, SUM(ac.amount)
FROM acc_trans ac
JOIN ap ON (ap.id = ac.trans_id)
GROUP BY 1, 2 
HAVING SUM(ac.amount) <> 0

ORDER BY 3

Orphan Transactions

SELECT * FROM acc_trans
WHERE trans_id NOT IN (
  SELECT id FROM ar
    UNION ALL 
  SELECT id FROM ap
    UNION ALL
  SELECT id FROM gl
)

Due to a bug/gotcha in orders handling in official sql-ledger, parts onhand can go out of sync from actual transactions. Following query will help you find the correct onhand quantity for a given assembly.

SELECT 'Purchased', SUM(0-qty) 
FROM invoice 
WHERE parts_id = (SELECT id FROM parts WHERE partnumber='TW01')
AND trans_id IN (SELECT id FROM ap)

UNION ALL

SELECT 'Sold', SUM(0-qty)
FROM invoice
WHERE parts_id IN (SELECT aid FROM assembly WHERE parts_id = (SELECT id FROM parts WHERE partnumber='TW01'))
AND trans_id IN (SELECT id FROM ar)

UNION ALL

SELECT 'Onhand', SUM(0-onhand)
FROM parts
WHERE id IN (SELECT aid FROM assembly WHERE parts_id = (SELECT id FROM parts WHERE partnumber='TW01'));