Sales Orders Age Calculation Using SuiteQL

When working with NetSuite, it’s essential to track the age of transactions, especially Sales Orders. Calculating the age, in days, of Sales Orders allows businesses to monitor how long these orders have been open and take appropriate actions if necessary. Using SuiteQL, you can efficiently retrieve this data with a simple query. Below is the SQL query you can use:

SELECT
    TranDate AS Date,
    trandisplayname AS SONumber,
    TRUNC(SYSDATE) - TranDate AS Age,
    BUILTIN.DF(entity) AS Customer
FROM
    Transaction
WHERE
    type = 'SalesOrd'
ORDER BY
    TranDate;

This query calculates the age of Sales Orders based on the transaction date (TranDate). The current system date (SYSDATE) is used to determine the age by subtracting the transaction date from today’s date.

Key Elements of the Query:

TranDate as Date:

• The transaction date field provides the date when the Sales Order was created.
• Renaming it as “Date” in the query output makes the result more user-friendly.

trandisplayname as SONumber:

• This field displays the Sales Order number.
• Renaming it to “SONumber” clarifies its purpose.

TRUNC(SYSDATE) – TranDate as Age:

• Using SYSDATE, which represents the current system date, and subtracting the TranDate gives the age in days.
• TRUNC(SYSDATE) ensures that only the date portion (ignoring time) is used in the calculation for consistency.

BUILTIN.DF(entity) as Customer:

• The entity field represents the customer associated with the Sales Order.
• The BUILTIN.DF() function fetches a human-readable display value for the customer.

WHERE type = ‘SalesOrd’:

• Filters the transactions to include only those of the type “Sales Order.”

ORDER BY TranDate:

• Sorts the results by the transaction date in ascending order, making it easier to view older Sales Orders first.