Tahlia Trading ledger

SELECT l.account_code, a.account_name, a.account_type,
       SUM(l.debit) AS total_debit, SUM(l.credit) AS total_credit,
       SUM(l.debit - l.credit) AS net_debit
FROM journal_lines AS l
JOIN journal_entries AS e ON e.entry_id = l.entry_id
JOIN gl_accounts AS a ON a.account_code = l.account_code
WHERE e.status = 'posted'
GROUP BY l.account_code, a.account_name, a.account_type
ORDER BY l.account_code;

SELECT e.period_id,
       SUM(CASE WHEN a.account_type = 'revenue' THEN l.credit - l.debit ELSE 0 END) AS revenue,
       SUM(CASE WHEN a.account_type = 'expense' THEN l.debit - l.credit ELSE 0 END) AS expenses,
       SUM(CASE WHEN a.account_type = 'revenue' THEN l.credit - l.debit
                WHEN a.account_type = 'expense' THEN l.credit - l.debit ELSE 0 END) AS net_profit
FROM journal_lines AS l
JOIN journal_entries AS e ON e.entry_id = l.entry_id
JOIN gl_accounts AS a ON a.account_code = l.account_code
WHERE e.status = 'posted'
GROUP BY e.period_id
ORDER BY e.period_id;

WITH actual AS (
  SELECT l.cost_center_code, SUM(l.debit - l.credit) AS actual
  FROM journal_lines l
  JOIN journal_entries e ON e.entry_id = l.entry_id
  JOIN gl_accounts a ON a.account_code = l.account_code
  JOIN fiscal_periods p ON p.period_id = e.period_id
  WHERE e.status = 'posted' AND a.account_type = 'expense' AND p.fiscal_year = 2025
  GROUP BY l.cost_center_code
), budget AS (
  SELECT b.cost_center_code, SUM(b.amount) AS budget
  FROM budgets b JOIN gl_accounts a ON a.account_code = b.account_code
  WHERE b.fiscal_year = 2025 AND a.account_type = 'expense'
  GROUP BY b.cost_center_code
)
SELECT COALESCE(a.cost_center_code, b.cost_center_code) AS cost_center_code,
       b.budget, a.actual, a.actual - b.budget AS variance
FROM actual a FULL JOIN budget b ON b.cost_center_code = a.cost_center_code
ORDER BY 1;

SELECT v.vendor_name, i.invoice_number, i.due_date, i.amount,
       i.amount - COALESCE(SUM(p.amount), 0) AS outstanding
FROM vendor_invoices AS i
JOIN vendors AS v ON v.vendor_id = i.vendor_id
LEFT JOIN vendor_payments AS p ON p.invoice_id = i.invoice_id
WHERE i.status <> 'paid'
GROUP BY v.vendor_name, i.invoice_id, i.invoice_number, i.due_date, i.amount
ORDER BY i.due_date;

SELECT s.statement_date, s.description, s.amount, s.bank_reference
FROM bank_statement_lines AS s
LEFT JOIN vendor_payments AS p ON p.bank_reference = s.bank_reference
WHERE s.amount < 0 AND p.payment_id IS NULL
ORDER BY s.statement_date;