SQL for Accountants in Malaysia: Queries Finance Teams Actually Use

SQL lets accountants ask the accounting or ERP database for exactly the transactions they need, instead of waiting for exports and rebuilding the same Excel workings every month. For a Malaysian finance team, the quickest wins are trial balance checks, aged receivables, duplicate invoice tests and reconciliations, all done with read-only access so nothing in the books can change.

At a glance

• What to learn first: SELECT, WHERE, GROUP BY, JOIN, CASE and date functions. That covers most finance queries.
• Where it pays off: month-end close, receivables follow-up, audit requests and reconciliations.
• The safety rule: read-only access, ideally to a reporting copy or replica, never the live database with write rights.
• Practice first: use SQL Server Express and Microsoft's sample databases before touching company data.
• Time needed: plan a few weeks of steady practice before you rely on your own queries for reporting.

Why finance teams are learning SQL

Excel is still the finance team's home, and it should be. SQL does not replace it. It replaces the slow part: exporting reports, deleting header rows, VLOOKUP chains and copying last month's workbook.

Three things make SQL worth the effort for accountants:

• Repeatability. A saved query runs the same logic every month. That is easier to review and defend than a workbook that has been edited by five people.
• Detail on demand. Auditors and managers ask follow-up questions. With SQL you can answer "which invoices make up that balance?" in minutes.
• More data to match. Malaysia's e-Invoice rollout by LHDN, implemented in stages, adds near real-time validated transaction records that someone has to match against the ledger. Matching large lists is exactly what SQL is good at.

This article is general information, not tax or accounting advice. Your firm's policies and standards decide how balances are treated.

Start safely: get read-only access

Before you write a single query, agree access with IT. Ask for three things.

1. A read-only login. In SQL Server, members of the fixed db_datareader role can read all data in all user tables and views. Better still, ask IT to create a custom role that can only read a reporting schema or a set of views:

-- Run by the database administrator, not by finance users
CREATE ROLE finance_reader;
GRANT SELECT ON SCHEMA::reporting TO finance_reader;
ALTER ROLE finance_reader ADD MEMBER [CORP\finance.analyst];

2. A copy or replica to query. If the system runs on an Always On availability group or on Azure SQL Database with read scale-out, adding ApplicationIntent=ReadOnly to the connection sends your queries to a read-only replica, away from the users posting transactions. Otherwise ask for a nightly restored copy.

3. Agreed rules. Never run UPDATE, INSERT or DELETE against an accounting database. Many vendors only support changes made through the application itself. Avoid heavy queries during posting runs or month-end processing. And limit the columns you pull: customer and employee records contain personal data protected under the PDPA, so select only what you need. Our guide to PDPA and AI tools at work explains why that matters before you paste results into an AI assistant.

Learn the shape of your accounting database

Every system names its tables differently. Ask the vendor or IT for a data dictionary rather than guessing. You can also search the catalogue yourself:

SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%invoice%' OR TABLE_NAME LIKE '%journal%'
ORDER BY TABLE_SCHEMA, TABLE_NAME;

The examples below use a simplified, made-up schema with tables for accounts, journal headers and lines, customers, invoices, receipt allocations and supplier invoices. Map them to your own system before running anything, and always tie your first results back to the system's standard report.

Query 1: trial balance and unbalanced journals

A trial balance is a GROUP BY on the journal lines:

SELECT a.AccountCode, a.AccountName,
       SUM(l.Debit) AS TotalDebit,
       SUM(l.Credit) AS TotalCredit,
       SUM(l.Debit - l.Credit) AS Balance
FROM gl.JournalLine AS l
JOIN gl.JournalHeader AS h ON h.JournalId = l.JournalId
JOIN gl.Account AS a ON a.AccountId = l.AccountId
WHERE h.Status = 'Posted'
  AND h.PostingDate < '2026-10-01'
GROUP BY a.AccountCode, a.AccountName
ORDER BY a.AccountCode;

A well-run system should never post an unbalanced journal, but imports, interfaces and migrated data sometimes do. This finds them:

SELECT JournalId, SUM(Debit) - SUM(Credit) AS Difference
FROM gl.JournalLine
GROUP BY JournalId
HAVING SUM(Debit) <> SUM(Credit);

Query 2: aged receivables as at a date

Ageing is where SQL saves the most time. The logic: take each invoice, subtract receipts allocated to it up to the as-at date, then put the balance into buckets by days past the due date.

DECLARE @AsAt date = '2026-09-30';

WITH OpenItems AS (
    SELECT i.CustomerId, i.InvoiceNo, i.DueDate,
           i.TotalAmount - COALESCE(SUM(al.Amount), 0) AS Balance
    FROM ar.Invoice AS i
    LEFT JOIN ar.Allocation AS al
           ON al.InvoiceId = i.InvoiceId
          AND al.AllocationDate < DATEADD(day, 1, @AsAt)
    WHERE i.InvoiceDate < DATEADD(day, 1, @AsAt)
    GROUP BY i.CustomerId, i.InvoiceNo, i.DueDate, i.TotalAmount
    HAVING i.TotalAmount - COALESCE(SUM(al.Amount), 0) <> 0
)
SELECT c.CustomerName,
       SUM(CASE WHEN DATEDIFF(day, o.DueDate, @AsAt) <= 0 THEN o.Balance ELSE 0 END) AS NotYetDue,
       SUM(CASE WHEN DATEDIFF(day, o.DueDate, @AsAt) BETWEEN 1 AND 30 THEN o.Balance ELSE 0 END) AS Days1To30,
       SUM(CASE WHEN DATEDIFF(day, o.DueDate, @AsAt) BETWEEN 31 AND 60 THEN o.Balance ELSE 0 END) AS Days31To60,
       SUM(CASE WHEN DATEDIFF(day, o.DueDate, @AsAt) BETWEEN 61 AND 90 THEN o.Balance ELSE 0 END) AS Days61To90,
       SUM(CASE WHEN DATEDIFF(day, o.DueDate, @AsAt) > 90 THEN o.Balance ELSE 0 END) AS Over90,
       SUM(o.Balance) AS TotalOutstanding
FROM OpenItems AS o
JOIN ar.Customer AS c ON c.CustomerId = o.CustomerId
GROUP BY c.CustomerName
ORDER BY TotalOutstanding DESC;

Three decisions to agree with your financial controller first:

• Age from due date or invoice date? Credit control usually wants due date; some management reports use invoice date.
• Credit notes and unallocated receipts. Decide whether they reduce the oldest bucket or show separately.
• Foreign currency invoices. Age in the invoice currency, then convert at the rate your policy specifies.

Finish by comparing the ageing total with the receivables control account in the trial balance. If they do not agree, the difference is your reconciliation list.

Query 3: possible duplicate supplier invoices

Paying the same invoice twice is a classic control failure, and auditors test for it. This flags the same supplier invoice number entered more than once:

SELECT SupplierId, SupplierInvoiceNo,
       COUNT(*) AS TimesEntered,
       SUM(Amount) AS TotalAmount
FROM ap.SupplierInvoice
GROUP BY SupplierId, SupplierInvoiceNo
HAVING COUNT(*) > 1;

Duplicates often hide behind small differences, such as "INV-001" and "INV001". A second pass that matches on supplier, amount and invoice date catches many of those.

Query 4: a running balance for an account statement

Window functions let you produce a running balance without spreadsheet formulas. The OVER clause defines the window:

SELECT h.PostingDate, h.JournalNo, l.Description,
       l.Debit, l.Credit,
       SUM(l.Debit - l.Credit) OVER (
           ORDER BY h.PostingDate, h.JournalNo, l.LineNumber
           ROWS UNBOUNDED PRECEDING) AS RunningBalance
FROM gl.JournalLine AS l
JOIN gl.JournalHeader AS h ON h.JournalId = l.JournalId
WHERE l.AccountId = 1200 AND h.Status = 'Posted'
ORDER BY h.PostingDate, h.JournalNo, l.LineNumber;

A SQL bank reconciliation uses a different pattern: import the bank statement file into a table, LEFT JOIN it to the cash book on amount and a date window, then list the items with no match on either side.

Mistakes that make finance queries wrong

Most wrong numbers from SQL come from a handful of traps. Check for these before you send anything to a manager or auditor:

• Joins that multiply rows. Joining invoices to receipts when one invoice has three receipts repeats the invoice amount three times. Aggregate the receipts first, as the ageing query does inside the CTE, then join.
• Missing NULL handling. An invoice with no receipts returns NULL from SUM, and NULL minus anything is NULL. Wrap sums in COALESCE.
• Date filters on datetime columns. PostingDate <= '2026-09-30' excludes anything posted later that day if the column holds a time. Use PostingDate < '2026-10-01' instead.
• Draft, held and reversed entries. Filter on posting status, and decide how reversals are treated, or your totals will not match the system.
• More than one company in the database. Group companies often share one database. Always filter on the company or entity code.
• Rounding. Round only at the final step. Rounding each line first creates small differences that waste an afternoon.

The quickest test of any new query is to reproduce a number you already trust, such as last month's closing balance from the system's own report.

Practise without touching company data

You can build all of these skills on your own laptop:

• SQL Server Express is Microsoft's free, entry-level edition for learning, and in SQL Server 2025 it supports databases up to 50 GB, according to What's new in SQL Server 2025. The free Developer editions include full features but are licensed for development and test only.
• The AdventureWorks sample databases include customers and sales orders to practise grouping, joins and date logic.
• SQL Server Management Studio or the MSSQL extension for Visual Studio Code to write and save queries.

Good habits from day one: comment every query, save it in a shared folder, have a colleague review anything that feeds a report, and record the date and filters used for each extract.

A six-week learning path for finance staff

When your queries start feeding dashboards, our guide to when to move from Excel to Power BI is a useful next read.

How CertTulen Academy can help

Microsoft SQL Server Fundamentals is a three-day, hands-on starting point for writing queries like these. For people who will own reporting databases, the SQL Server Beginner-to-Master Bootcamp goes much further. CertTulen Academy teaches both live and instructor-led, online or as a private cohort for your finance team. CertTulen is an HRD Corp accredited training provider, and many of its professional courses can be claimed by HRD Corp registered employers, so ask us which courses are claimable. Contact us to plan a session.