# Table of Contents - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) - [SuiteQL Query Library | Tim Dietrich](#suiteql-query-library-tim-dietrich) --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- 105 SuiteQL queries are currently available. [Account Subsidiary Map](/suiteql-query-library/?query=account-subsidiary-map) Returns a list of general ledger accounts and the subsidiaries that they are mapped to. [Accounting Periods](/suiteql-query-library/?query=accounting-periods) Returns basic information about accounting periods. [Accounts Payable: Aging Details](/suiteql-query-library/?query=accounts-payable-aging-details) Returns detailed Accounts Payable information based on a specific vendor. [Accounts Payable: Aging Summary](/suiteql-query-library/?query=accounts-payable-aging-summary) Returns summarized Accounts Payable information based on a specific vendor. [Accounts Receivable: Aging Details](/suiteql-query-library/?query=accounts-receivable-aging-details) Returns detailed Accounts Receivable information based on a specific customer. [Accounts Receivable: Aging Summary](/suiteql-query-library/?query=accounts-receivable-aging-summary) Returns summarized Accounts Receivable information based on a specific customer. [Assembly Item With Components](/suiteql-query-library/?query=assembly-items-with-components) Returns an Assembly Item and its components. [Bins: By Location](/suiteql-query-library/?query=bins-list) Returns a list of bins in a specified warehouse (location). [Bins: Content](/suiteql-query-library/?query=bins-content) Returns the contents of a specified bin. [Bins: Empty](/suiteql-query-library/?query=bins-empty) Returns a list of empty warehouse bins. [BUILTIN.RELATIVE\_RANGES Examples](/suiteql-query-library/?query=builtin-relative-ranges) Returns the start and end values for all relative date ranges that the BUILTIN.RELATIVE\_RANGES function currently supports. [Company Contacts](/suiteql-query-library/?query=company-contacts) Returns contacts associated with a specified company, including contact names and roles. [Company Features](/suiteql-query-library/?query=company-features) Returns a list of NetSuite features that are avaialble and/or enabled in the account. [Countries](/suiteql-query-library/?query=countries) Returns a list of countries that NetSuite supports. [Currencies](/suiteql-query-library/?query=currencies) Returns a list of currencies, with symbols, latest exchange rates, etc. [Currency Rates](/suiteql-query-library/?query=currency-rates) Returns historical currency exchange rates. [Custom Fields](/suiteql-query-library/?query=custom-fields) Returns all custom fields in the account. [Custom Lists](/suiteql-query-library/?query=custom-lists) Returns all custom lists in the account. [Custom Record Types](/suiteql-query-library/?query=custom-record-types) Returns all custom record types in the account. [Custom Segments](/suiteql-query-library/?query=custom-segments) Returns all custom segments, including the names of the tables in which the segment values are stored. [Customer Deposits](/suiteql-query-library/?query=customer-deposits) Returns all deposits received from a specific customer. [Customer Invoices: By Date Range](/suiteql-query-library/?query=customer-invoices) Returns all customer (accounts receivable) invoices for a given date range, with totals and statuses. [Customer Payments: Application Details](/suiteql-query-library/?query=customer-payments-application-details) Returns the invoices to which a customer payment was applied, including sales order numbers, customer PO numbers, and the amounts applied. [Customers: Billing Addresses](/suiteql-query-library/?query=customers-billing-address) Returns a list of customers and their default billto addresses. [Customers: Transaction Ledgers](/suiteql-query-library/?query=customer-transaction-ledger) Used for generatin a transaction ledger for a specified customer. [Customers: With Overdue Balances](/suiteql-query-library/?query=customers-with-overdue-balances) Returns customers that have overdue balances. [Dates and Times: Creation Examples](/suiteql-query-library/?query=date-time-creation-examples) Shows how you can create dates and timestamps based on strings. [Dates and Times: Format Models](/suiteql-query-library/?query=date-format-models) Returns examples of datetime format elements. [Deleted Records](/suiteql-query-library/?query=deleted-records) Returns information about records that have been deleted. [Employees: Emergency Contacts](/suiteql-query-library/?query=employees-emergency-contacts) Returns emergency contact information for employees. [Employees: Last Logins](/suiteql-query-library/?query=employees-last-logins) Returns all employees that have NetSuite access, and the date/time that they last logged in. [Employees: No Recent Logins](/suiteql-query-library/?query=employees-no-recent-logins) Returns employees that have not logged in recently. [Employees: Roles and Permissions](/suiteql-query-library/?query=employees-roles-permissions) Returns all employees, their assigned roles, and the permissions granted. [Employees: With Edit, View, Email URLs](/suiteql-query-library/?query=employees-with-edit-view-email-urls) Returns all employees, with edit, view, and email URLs in the results. [Entities: Types](/suiteql-query-library/?query=entity-types) Returns all entity types, with BUILTIN.DF and BUILTIN.CF values. [File Cabinet: Files By Name](/suiteql-query-library/?query=files-by-name) Returns information about a file stored in the File Cabinet, based on a specified file name. [File Cabinet: Top Level Folders](/suiteql-query-library/?query=file-cabinet-top-level-folders) Returns a list of folders in the root of the File Cabinet. [General Ledger Impact by Transaction Type](/suiteql-query-library/?query=general-ledger-impact-by-transaction-type) Returns all transaction types and the general ledger accounts that they post to. [General Ledger Transactions by Account and Date Range](/suiteql-query-library/?query=general-ledger-transactions-by-account-and-date) Returns all of the transactions that were posted to a specified general ledger account in a specified date range. [Hello World](/suiteql-query-library/?query=hello-world) An obligatory "Hello World" query in SuiteQL fashion. [HTTP Requests - Outbound](/suiteql-query-library/?query=http-requests-outbound) Returns information about outbound HTTP requests being made. [Item Fulfillments: for Specified Sales Order](/suiteql-query-library/?query=item-fulfillments-for-sales-order) Returns all item fulfillments for a specified Sales Order ID. [Items: Aggregate Information by Location](/suiteql-query-library/?query=items-aggregate-information-by-location) Returns location-specific inventory information for a specified item. [Items: Customer Upsells](/suiteql-query-library/?query=items-customer-upsells) Returns a list of potential upsell items and item categories for a specific customer. [Items: Customers That Purchased A Specific Item](/suiteql-query-library/?query=items-customers-purchased) Returns a list of customers that purchased a specified item. [Items: Expired or Expiring Soon](/suiteql-query-library/?query=items-expired-or-expiring-soon) Returns items that have expired, or are about to expire within a certain number of days. [Items: Frequently Sold Together](/suiteql-query-library/?query=items-sold-together) Returns a list of items that are frequently sold along with a specified item. This can be used to implement the "cross-selling" sales technique. [Items: Inventory Impact Transactions](/suiteql-query-library/?query=item-inventory-impact-transactions) Returns transactions that impacted an item's inventory level. [Items: In Alternate Warehouse Bins](/suiteql-query-library/?query=items-in-alternate-bins) Returns items that are in stock and stored in bins that are not the item's primary bin. [Items: Inventory Analysis](/suiteql-query-library/?query=items-inventory-analysis) Designed for inventory analysis, this query returns active items, various quantities (on hand, committed, available, reorder point, preferred stock level), the preferred vendor, the average resale price and purchase cost, quantities sold (in the last 30 days, 90 days, year, and lifetime), and more. [Items: Item Group Members](/suiteql-query-library/?query=item-group-members) Returns members of an Item Group, including their list prices. [Items: Item Group Prices](/suiteql-query-library/?query=item-group-prices) Returns the price for a specified Item Group. [Items: Item Groups](/suiteql-query-library/?query=item-groups) Returns a list of items that are setup as Item Groups. [Items: Item Information](/suiteql-query-library/?query=item-information) Returns detailed information about items that have been created or updated in the past 7 days. Item information includes the preferred vendor. Used in project involving development of a custom Product Information Manager (PIM). [Items: Pricing](/suiteql-query-library/?query=items-price-levels) Returns price information, including price levels, for a given item. [Items: Item Types and Counts](/suiteql-query-library/?query=items-item-type-counts) Returns a list of the types of items that are setup, and the count for each item type. [Items: Returned Items by Date Range](/suiteql-query-library/?query=items-returns) Returns a list of items that were returned the most frequently in the previous year. [Items: Quantity Available Has Changed](/suiteql-query-library/?query=items-availability-changed) Returns a list of items whose quantity available has changed since a specified date. [Items: Running Inventory Balance](/suiteql-query-library/?query=item-running-inventory-balance) Returns the running inventory balance for a specified item. [Items: Standalone Items](/suiteql-query-library/?query=standalone-items) Returns a list of items that exist entirely on their own, independent of a parent or child item. [Items: Summarized by Type](/suiteql-query-library/?query=item-type-summary) Returns the number of active items group by item type. [Items: Vendors](/suiteql-query-library/?query=item-vendors) Returns a list of vendors that can provide a specified item. [Items: With Expiration Dates](/suiteql-query-library/?query=items-with-expiration-dates) Returns items that have expiration dates. [Items: Without Preferred Bins](/suiteql-query-library/?query=items-without-preferred-bins) Returns items that do not have a preferred bin. [Journal Entries: In Date Range](/suiteql-query-library/?query=journal-entries-in-date-range) Returns journal entry transactions in a given date range. [Journal Entries: With Details](/suiteql-query-library/?query=journal-entries-with-details) Returns journal entry transactions in a given date range, with line level details. [Leads: For A Specific Sales Rep](/suiteql-query-library/?query=leads-for-salesrep) Returns leads assigned to a specified sales rep. [Leads: Summarized by Sales Rep](/suiteql-query-library/?query=leads-salesrep-summarized) Returns a summarized list of sales reps and the number of leads that they've been assigned. [Locations](/suiteql-query-library/?query=locations) Returns a list of locations, including the main address. [Matrix Items: Child Items by Parent](/suiteql-query-library/?query=matrix-items-child-items-for-parent) Returns all child items for a specified parent item. [Matrix Items: Child Items](/suiteql-query-library/?query=matrix-items-child-items) Returns a child item and its parent item. [Matrix Items: Parent Items](/suiteql-query-library/?query=matrix-items-parent-items) Returns all parent items. [Packages: Pacejet Package Information](/suiteql-query-library/?query=pacejet-package-information) Returns information about item fulfillments and related Pacejet packages. [Pagination and Record Count Example](/suiteql-query-library/?query=pagination-recordcount-example) Returns paginated results that includes the total number of rows found. [Phone Calls](/suiteql-query-library/?query=phone-calls) Returns calls related to a specific company, contact, etc. [Purchase Orders: Summarized by Status](/suiteql-query-library/?query=purchase-orders-summarized-status) Returns the various statuses of purchase orders, with the number of orders in each status, and the total amounts. [Purchase Orders: Item Receipts](/suiteql-query-library/?query=purchase-orders-item-receipts) Returns items that have been received against POs in a given date range. [Purchase Orders: Related Transactions](/suiteql-query-library/?query=purchase-orders-related-transactions) Returns a list of a purchase order's related transactions, including prepayments, item receipts, vendor bills, and more. [Returns Authorizations: In Date Range](/suiteql-query-library/?query=rmas-in-date-range) Returns a list of Return Authorizations (RAs / RMAs) that have been issued in a specified date range. [Returns Authorizations: Summarized by Customer](/suiteql-query-library/?query=rmas-summarized-customer) Returns a list of Return Authorizations (RAs / RMAs) that have been issued in a specified date range, summarized by customer. [Returns Authorizations: Summarized by Item](/suiteql-query-library/?query=rmas-summarized-item) Returns a list of Return Authorizations (RAs / RMAs) that have been issued in a specified date range, summarized by item. [Roles: Active Roles Not Used Recently](/suiteql-query-library/?query=roles-active-roles-not-used-recently) Returns the active roles that haven't been used in the past 90 days. [Roles: Active Roles And The Date Last Used](/suiteql-query-library/?query=roles-active-roles-with-date-last-used) Returns all active roles, and includes the date that each role was most recently used. [ROLLUP: Oracle Command Example](/suiteql-query-library/?query=rollup-example) Used to append a summary row to the result set. [Sales Order: With Sales Tax](/suiteql-query-library/?query=sales-order-with-sales-tax) Returns header-level values for a specified Sales Order, including the Tax Total value sourced from the related TransactionLine records. [Sales Orders: Shipped](/suiteql-query-library/?query=sales-orders-shipped) Returns sales orders that shipped in a specified date range. [Sales Orders: Summarized by Status](/suiteql-query-library/?query=sales-orders-summarized-status) Returns the various statuses of sales orders, with the number of orders in each status, and the total amounts. [Sales Orders: Top Orders for the Current Day](/suiteql-query-library/?query=sales-orders-top-for-day) Returns the top sales orders of the day, based on the estimated gross profit. [Sales Pipeline](/suiteql-query-library/?query=sales-pipeline) Returns a list of sales orders that are scheduled to ship in a specified date range. [Sales Terms](/suiteql-query-library/?query=sales-terms) Returns a list of sales terms (also referred to as "Terms of Payment") that are setup in the instance. [Shipments: Details](/suiteql-query-library/?query=shipments-detailed) Returns detailed shipment information based on date criteria. Includes the customer, sales order, customer po, the warehouse, that it shipped from, carrier / method, total weight, number of items shipped, tracking number(s), and the items / quantities that shipped. [Shipments: Inbound](/suiteql-query-library/?query=shipments-inbound) Returns all inbound shipments, including expected and actual ship dates, expected delivery dates, and more. [Shipments: Outbound](/suiteql-query-library/?query=shipments) Returns all shipments (item fulfillments) made within a specified date range. Includes tracking numbers, package information (ship from and to addresses, weight, shipping / handles charges), and more. [States](/suiteql-query-library/?query=states) Returns a list of states that NetSuite supports. [Support Cases: Closed: Summarized by Assignee](/suiteql-query-library/?query=support-cases-closed-summarized-assignee) Returns closed support cases summarized by the employee that they are assigned to. [Support Cases: Open: Detailed](/suiteql-query-library/?query=support-cases-open-detailed) Returns details about open support cases. [Support Cases: Open: Summarized by Assignee](/suiteql-query-library/?query=support-cases-open-summarized-assignee) Returns open support cases summarized by the employee that they are assigned to. [Support Cases: Open: Summarized by Status](/suiteql-query-library/?query=support-cases-open-summarized-status) Returns open support cases summarized by status. [Taxes - AvaTax Transaction Logs](/suiteql-query-library/?query=avatax-transaction-logs) Returns information about the AvaTax API calls that were made for a specified transaction. [Transaction Line Usage](/suiteql-query-library/?query=transaction-line-usage) Returns the number of Transaction Lines generated in the last 6 months, grouped by transaction type. [Transaction Counts: By Type and Status](/suiteql-query-library/?query=transaction-counts-by-type-status) Returns the total number of transactions in each type / status combination. [Transactions: GL Impact](/suiteql-query-library/?query=transaction-gl-impact) Returns the GL impact for a specified transaction. [Transactions: GL Impact: Revisited](/suiteql-query-library/?query=transaction-gl-impact-revisited) Returns the GL impact for a specified transaction. [Transactions: Statuses](/suiteql-query-library/?query=transaction-statuses) Returns a list status codes for all transaction types. [Vendors: Vendor Information](/suiteql-query-library/?query=vendor-information) Returns detailed information about vendors that have been created or updated in the past 7 days. Used in project involving development of a custom Product Information Manager (PIM). About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Accounting Periods Returns basic information about accounting periods. SELECT AccountingPeriod.ID, AccountingPeriod.PeriodName, ParentPeriod.ID AS ParentPeriodID, ParentPeriod.PeriodName AS ParentPeriodName, AccountingPeriod.StartDate, AccountingPeriod.EndDate, AccountingPeriod.IsPosting, AccountingPeriod.IsAdjust, AccountingPeriod.AllLocked, AccountingPeriod.ARLocked, AccountingPeriod.APLocked, AccountingPeriod.AllowNonGLChanges, AccountingPeriod.LastModifiedDate, AccountingPeriod.Closed, AccountingPeriod.ClosedOnDate FROM AccountingPeriod INNER JOIN AccountingPeriod AS ParentPeriod ON ( ParentPeriod.ID = AccountingPeriod.Parent ) WHERE ( AccountingPeriod.IsInactive = 'F' ) AND ( AccountingPeriod.IsPosting = 'T' ) AND ( AccountingPeriod.IsQuarter = 'F' ) ORDER BY AccountingPeriod.StartDate [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Account Subsidiary Map Returns a list of general ledger accounts and the subsidiaries that they are mapped to. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-accounts-by-subsidiary/ SELECT Account, BUILTIN.DF( Account ) AS AccountName, Subsidiary, BUILTIN.DF( Subsidiary ) AS SubsidiaryName FROM AccountSubsidiaryMap [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Accounts Payable: Aging Details Returns detailed Accounts Payable information based on a specific vendor. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-accounts-payable-aging/ SELECT BUILTIN.DF( Transaction.Entity ) AS Vendor, Transaction.Type AS TransactionType, Transaction.TranDate AS Date, Transaction.TranID AS DocumentNumber, Transaction.DueDate, ( TRUNC( SYSDATE ) - Transaction.DueDate ) AS Age, ( COALESCE( TransactionAccountingLine.AmountUnpaid, 0 ) - COALESCE( TransactionAccountingLine.PaymentAmountUnused, 0 ) ) AS OpenBalance FROM Transaction INNER JOIN TransactionAccountingLine ON ( TransactionAccountingLine.Transaction = Transaction.ID ) WHERE ( Transaction.Entity = 999999 ) AND ( Transaction.Posting = 'T' ) AND ( Transaction.Voided = 'F' ) AND ( ( TransactionAccountingLine.AmountUnpaid <> 0 ) OR ( TransactionAccountingLine.PaymentAmountUnused <> 0 ) ) ORDER BY Transaction.TranDate, Transaction.TranID [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Accounts Receivable: Aging Details Returns detailed Accounts Receivable information based on a specific customer. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-accounts-receivable-aging/ SELECT BUILTIN.DF( Transaction.Entity ) AS Customer, Transaction.Type AS TransactionType, Transaction.TranDate AS Date, Transaction.TranID AS DocumentNumber, Transaction.OtherRefNum AS PONumber, Transaction.DueDate, ( TRUNC( SYSDATE ) - Transaction.DueDate ) AS Age, ( COALESCE( TransactionAccountingLine.AmountUnpaid, 0 ) - COALESCE( TransactionAccountingLine.PaymentAmountUnused, 0 ) ) AS OpenBalance FROM Transaction INNER JOIN TransactionAccountingLine ON ( TransactionAccountingLine.Transaction = Transaction.ID ) WHERE ( Transaction.Entity = 999999 ) AND ( Transaction.Posting = 'T' ) AND ( Transaction.Voided = 'F' ) AND ( ( TransactionAccountingLine.AmountUnpaid <> 0 ) OR ( TransactionAccountingLine.PaymentAmountUnused <> 0 ) ) ORDER BY Transaction.TranDate, Transaction.TranID [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Assembly Item With Components Returns an Assembly Item and its components. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Source: https://timdietrich.me/blog/netsuite-suiteql-assembly-items/ SELECT '[View](/app/common/item/item.nl?id=' || AssemblyItem.ID || ') ' AS AssemblyItemLink, AssemblyItem.ID AS AssemblyItemID, AssemblyItem.ItemID AS AssemblyItemItemID, AssemblyItem.Description AS AssemblyItemDec, '[View](/app/common/item/item.nl?id=' || AssemblyItemMember.Item || ') ' AS MemberItemLink, AssemblyItemMember.LineNumber, AssemblyItemMember.Item AS MemberItemID, BUILTIN.DF( AssemblyItemMember.Item ) AS MemberItemItemID, AssemblyItemMember.Quantity AS MemberItemQty, BUILTIN.DF( AssemblyItemMember.MemberUnit ) AS MemberItemUOM, AssemblyItemMember.ItemSource AS MemberItemSource, MemberItem.ItemType AS MemberItemType, MemberItem.Description AS MemberItemDesc FROM Item AS AssemblyItem INNER JOIN AssemblyItemMember ON ( AssemblyItemMember.ParentItem = AssemblyItem.ID ) INNER JOIN Item AS MemberItem ON ( MemberItem.ID = AssemblyItemMember.Item ) WHERE ( AssemblyItem.ID = 999999 ) ORDER BY AssemblyItemMember.LineNumber [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Accounts Payable: Aging Summary Returns summarized Accounts Payable information based on a specific vendor. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-accounts-payable-aging/ SELECT BUILTIN.DF( Transaction.Entity ) AS Vendor, SUM( CASE WHEN ( TRUNC( SYSDATE ) - Transaction.DueDate ) < 1 THEN COALESCE( TransactionAccountingLine.AmountUnpaid, 0 ) - COALESCE( TransactionAccountingLine.PaymentAmountUnused, 0 ) ELSE 0 END ) AS Current, SUM( CASE WHEN ( TRUNC( SYSDATE ) - Transaction.DueDate ) BETWEEN 1 AND 30 THEN COALESCE( TransactionAccountingLine.AmountUnpaid, 0 ) - COALESCE( TransactionAccountingLine.PaymentAmountUnused, 0 ) ELSE 0 END ) AS Balance30, SUM( CASE WHEN ( TRUNC( SYSDATE ) - Transaction.DueDate ) BETWEEN 31 AND 60 THEN COALESCE( TransactionAccountingLine.AmountUnpaid, 0 ) - COALESCE( TransactionAccountingLine.PaymentAmountUnused, 0 ) ELSE 0 END ) AS Balance60, SUM( CASE WHEN ( TRUNC( SYSDATE ) - Transaction.DueDate ) BETWEEN 61 AND 90 THEN COALESCE( TransactionAccountingLine.AmountUnpaid, 0 ) - COALESCE( TransactionAccountingLine.PaymentAmountUnused, 0 ) ELSE 0 END ) AS Balance90, SUM( CASE WHEN ( TRUNC( SYSDATE ) - Transaction.DueDate ) > 90 THEN COALESCE( TransactionAccountingLine.AmountUnpaid, 0 ) - COALESCE( TransactionAccountingLine.PaymentAmountUnused, 0 ) ELSE 0 END ) AS Balance90Plus, SUM ( COALESCE( TransactionAccountingLine.AmountUnpaid, 0 ) - COALESCE( TransactionAccountingLine.PaymentAmountUnused, 0 ) ) AS Total FROM Transaction INNER JOIN TransactionAccountingLine ON ( TransactionAccountingLine.Transaction = Transaction.ID ) INNER JOIN Vendor ON ( Vendor.ID = Transaction.Entity ) WHERE ( Transaction.Posting = 'T' ) AND ( Transaction.Voided = 'F' ) AND ( ( TransactionAccountingLine.AmountUnpaid <> 0 ) OR ( TransactionAccountingLine.PaymentAmountUnused <> 0 ) ) GROUP BY BUILTIN.DF( Transaction.Entity ) ORDER BY BUILTIN.DF( Transaction.Entity ) [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Accounts Receivable: Aging Summary Returns summarized Accounts Receivable information based on a specific customer. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-accounts-receivable-aging/ SELECT BUILTIN.DF( Transaction.Entity ) AS Customer, SUM( CASE WHEN ( TRUNC( SYSDATE ) - Transaction.DueDate ) < 1 THEN COALESCE( TransactionAccountingLine.AmountUnpaid, 0 ) - COALESCE( TransactionAccountingLine.PaymentAmountUnused, 0 ) ELSE 0 END ) AS Current, SUM( CASE WHEN ( TRUNC( SYSDATE ) - Transaction.DueDate ) BETWEEN 1 AND 30 THEN COALESCE( TransactionAccountingLine.AmountUnpaid, 0 ) - COALESCE( TransactionAccountingLine.PaymentAmountUnused, 0 ) ELSE 0 END ) AS Balance30, SUM( CASE WHEN ( TRUNC( SYSDATE ) - Transaction.DueDate ) BETWEEN 31 AND 60 THEN COALESCE( TransactionAccountingLine.AmountUnpaid, 0 ) - COALESCE( TransactionAccountingLine.PaymentAmountUnused, 0 ) ELSE 0 END ) AS Balance60, SUM( CASE WHEN ( TRUNC( SYSDATE ) - Transaction.DueDate ) BETWEEN 61 AND 90 THEN COALESCE( TransactionAccountingLine.AmountUnpaid, 0 ) - COALESCE( TransactionAccountingLine.PaymentAmountUnused, 0 ) ELSE 0 END ) AS Balance90, SUM( CASE WHEN ( TRUNC( SYSDATE ) - Transaction.DueDate ) > 90 THEN COALESCE( TransactionAccountingLine.AmountUnpaid, 0 ) - COALESCE( TransactionAccountingLine.PaymentAmountUnused, 0 ) ELSE 0 END ) AS Balance90Plus, SUM ( COALESCE( TransactionAccountingLine.AmountUnpaid, 0 ) - COALESCE( TransactionAccountingLine.PaymentAmountUnused, 0 ) ) AS Total FROM Transaction INNER JOIN TransactionAccountingLine ON ( TransactionAccountingLine.Transaction = Transaction.ID ) INNER JOIN Customer ON ( Customer.ID = Transaction.Entity ) WHERE ( Transaction.Posting = 'T' ) AND ( Transaction.Voided = 'F' ) AND ( ( TransactionAccountingLine.AmountUnpaid <> 0 ) OR ( TransactionAccountingLine.PaymentAmountUnused <> 0 ) ) GROUP BY BUILTIN.DF( Transaction.Entity ) ORDER BY BUILTIN.DF( Transaction.Entity ) [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Bins: By Location Returns a list of bins in a specified warehouse (location). \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-warehouse-bin-information/ SELECT Bin.ID AS BinID, Bin.BinNumber, Bin.Memo AS BinMemo FROM Bin WHERE ( Bin.Location = 1 ) ORDER BY Bin.BinNumber [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Bins: Empty Returns a list of empty warehouse bins. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-empty-warehouse-bins/ SELECT BUILTIN.DF( Bin.Location ) AS BinLocation, Bin.BinNumber FROM Bin LEFT OUTER JOIN ItemBinQuantity ON ( ItemBinQuantity.Bin = Bin.ID ) WHERE ( Bin.Location = 16 ) GROUP BY BUILTIN.DF( Bin.Location ), Bin.BinNumber HAVING SUM( ItemBinQuantity.OnHand ) = 0 ORDER BY BUILTIN.DF( Bin.Location ), Bin.BinNumber [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Bins: Content Returns the contents of a specified bin. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-warehouse-bin-information/ SELECT Bin.ID AS BinID, Bin.BinNumber, Bin.Memo AS BinMemo, ItemBinQuantity.OnHand, ItemBinQuantity.OnHandAvail FROM Bin INNER JOIN ItemBinQuantity ON ( ItemBinQuantity.Bin = Bin.ID ) WHERE ( Bin.Location = 1 ) AND ( ItemBinQuantity.Item = 101901 ) ORDER BY Bin.BinNumber [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Company Contacts Returns contacts associated with a specified company, including contact names and roles. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-company-contacts/ SELECT Company.ID AS Company, Company.EntityTitle AS CompanyName, CompanyContactRelationship.Contact, BUILTIN.DF( CompanyContactRelationship.Contact ) AS ContactName, CompanyContactRelationship.Role, BUILTIN.DF( CompanyContactRelationship.Role ) AS RoleName FROM Entity AS Company INNER JOIN CompanyContactRelationship ON ( CompanyContactRelationship.Company = Company.ID ) WHERE ( Company.ID = 999999 ) [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### BUILTIN.RELATIVE\_RANGES Examples Returns the start and end values for all relative date ranges that the BUILTIN.RELATIVE\_RANGES function currently supports. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-dynamic-calendar-date-ranges/ SELECT BUILTIN.RELATIVE\_RANGES( 'FHBL', 'START' ) AS FISCAL\_HALF\_BEFORE\_LAST\_START, BUILTIN.RELATIVE\_RANGES( 'FHBL', 'END' ) AS FISCAL\_HALF\_BEFORE\_LAST\_END, BUILTIN.RELATIVE\_RANGES( 'FHBLTD', 'START' ) AS FISCAL\_HALF\_BEFORE\_LAST\_TO\_DATE\_START, BUILTIN.RELATIVE\_RANGES( 'FHBLTD', 'END' ) AS FISCAL\_HALF\_BEFORE\_LAST\_TO\_DATE\_END, BUILTIN.RELATIVE\_RANGES( 'FQBL', 'START' ) AS FISCAL\_QUARTER\_BEFORE\_LAST\_START, BUILTIN.RELATIVE\_RANGES( 'FQBL', 'END' ) AS FISCAL\_QUARTER\_BEFORE\_LAST\_END, BUILTIN.RELATIVE\_RANGES( 'FQBLTD', 'START' ) AS FISCAL\_QUARTER\_BEFORE\_LAST\_TO\_DATE\_START, BUILTIN.RELATIVE\_RANGES( 'FQBLTD', 'END' ) AS FISCAL\_QUARTER\_BEFORE\_LAST\_TO\_DATE\_END, BUILTIN.RELATIVE\_RANGES( 'FYBL', 'START' ) AS FISCAL\_YEAR\_BEFORE\_LAST\_START, BUILTIN.RELATIVE\_RANGES( 'FYBL', 'END' ) AS FISCAL\_YEAR\_BEFORE\_LAST\_END, BUILTIN.RELATIVE\_RANGES( 'FYBLTD', 'START' ) AS FISCAL\_YEAR\_BEFORE\_LAST\_TO\_DATE\_START, BUILTIN.RELATIVE\_RANGES( 'FYBLTD', 'END' ) AS FISCAL\_YEAR\_BEFORE\_LAST\_TO\_DATE\_END, BUILTIN.RELATIVE\_RANGES( 'DAGO5', 'START' ) AS FIVE\_DAYS\_AGO\_START, BUILTIN.RELATIVE\_RANGES( 'DAGO5', 'END' ) AS FIVE\_DAYS\_AGO\_END, BUILTIN.RELATIVE\_RANGES( 'DFN5', 'START' ) AS FIVE\_DAYS\_FROM\_NOW\_START, BUILTIN.RELATIVE\_RANGES( 'DFN5', 'END' ) AS FIVE\_DAYS\_FROM\_NOW\_END, BUILTIN.RELATIVE\_RANGES( 'DAGO4', 'START' ) AS FOUR\_DAYS\_AGO\_START, BUILTIN.RELATIVE\_RANGES( 'DAGO4', 'END' ) AS FOUR\_DAYS\_AGO\_END, BUILTIN.RELATIVE\_RANGES( 'DFN4', 'START' ) AS FOUR\_DAYS\_FROM\_NOW\_START, BUILTIN.RELATIVE\_RANGES( 'DFN4', 'END' ) AS FOUR\_DAYS\_FROM\_NOW\_END, BUILTIN.RELATIVE\_RANGES( 'TWN3W', 'START' ) AS FOUR\_WEEKS\_STARTING\_THIS\_WEEK\_START, BUILTIN.RELATIVE\_RANGES( 'TWN3W', 'END' ) AS FOUR\_WEEKS\_STARTING\_THIS\_WEEK\_END, BUILTIN.RELATIVE\_RANGES( 'LBW', 'START' ) AS LAST\_BUSINESS\_WEEK\_START, BUILTIN.RELATIVE\_RANGES( 'LBW', 'END' ) AS LAST\_BUSINESS\_WEEK\_END, BUILTIN.RELATIVE\_RANGES( 'LFH', 'START' ) AS LAST\_FISCAL\_HALF\_START, BUILTIN.RELATIVE\_RANGES( 'LFH', 'END' ) AS LAST\_FISCAL\_HALF\_END, BUILTIN.RELATIVE\_RANGES( 'LFHLFY', 'START' ) AS LAST\_FISCAL\_HALF\_ONE\_FISCAL\_YEAR\_AGO\_START, BUILTIN.RELATIVE\_RANGES( 'LFHLFY', 'END' ) AS LAST\_FISCAL\_HALF\_ONE\_FISCAL\_YEAR\_AGO\_END, BUILTIN.RELATIVE\_RANGES( 'LFHTD', 'START' ) AS LAST\_FISCAL\_HALF\_TO\_DATE\_START, BUILTIN.RELATIVE\_RANGES( 'LFHTD', 'END' ) AS LAST\_FISCAL\_HALF\_TO\_DATE\_END, BUILTIN.RELATIVE\_RANGES( 'LFQ', 'START' ) AS LAST\_FISCAL\_QUARTER\_START, BUILTIN.RELATIVE\_RANGES( 'LFQ', 'END' ) AS LAST\_FISCAL\_QUARTER\_END, BUILTIN.RELATIVE\_RANGES( 'LFQLFY', 'START' ) AS LAST\_FISCAL\_QUARTER\_ONE\_FISCAL\_YEAR\_AGO\_START, BUILTIN.RELATIVE\_RANGES( 'LFQLFY', 'END' ) AS LAST\_FISCAL\_QUARTER\_ONE\_FISCAL\_YEAR\_AGO\_END, BUILTIN.RELATIVE\_RANGES( 'LFQTD', 'START' ) AS LAST\_FISCAL\_QUARTER\_TO\_DATE\_START, BUILTIN.RELATIVE\_RANGES( 'LFQTD', 'END' ) AS LAST\_FISCAL\_QUARTER\_TO\_DATE\_END, BUILTIN.RELATIVE\_RANGES( 'LFQFYBL', 'START' ) AS LAST\_FISCAL\_QUARTER\_TWO\_FISCAL\_YEARS\_AGO\_START, BUILTIN.RELATIVE\_RANGES( 'LFQFYBL', 'END' ) AS LAST\_FISCAL\_QUARTER\_TWO\_FISCAL\_YEARS\_AGO\_END, BUILTIN.RELATIVE\_RANGES( 'LFY', 'START' ) AS LAST\_FISCAL\_YEAR\_START, BUILTIN.RELATIVE\_RANGES( 'LFY', 'END' ) AS LAST\_FISCAL\_YEAR\_END, BUILTIN.RELATIVE\_RANGES( 'LFYTD', 'START' ) AS LAST\_FISCAL\_YEAR\_TO\_DATE\_START, BUILTIN.RELATIVE\_RANGES( 'LFYTD', 'END' ) AS LAST\_FISCAL\_YEAR\_TO\_DATE\_END, BUILTIN.RELATIVE\_RANGES( 'LM', 'START' ) AS LAST\_MONTH\_START, BUILTIN.RELATIVE\_RANGES( 'LM', 'END' ) AS LAST\_MONTH\_END, BUILTIN.RELATIVE\_RANGES( 'LMLFQ', 'START' ) AS LAST\_MONTH\_ONE\_FISCAL\_QUARTER\_AGO\_START, BUILTIN.RELATIVE\_RANGES( 'LMLFQ', 'END' ) AS LAST\_MONTH\_ONE\_FISCAL\_QUARTER\_AGO\_END, BUILTIN.RELATIVE\_RANGES( 'LMLFY', 'START' ) AS LAST\_MONTH\_ONE\_FISCAL\_YEAR\_AGO\_START, BUILTIN.RELATIVE\_RANGES( 'LMLFY', 'END' ) AS LAST\_MONTH\_ONE\_FISCAL\_YEAR\_AGO\_END, BUILTIN.RELATIVE\_RANGES( 'LMTD', 'START' ) AS LAST\_MONTH\_TO\_DATE\_START, BUILTIN.RELATIVE\_RANGES( 'LMTD', 'END' ) AS LAST\_MONTH\_TO\_DATE\_END, BUILTIN.RELATIVE\_RANGES( 'LMFQBL', 'START' ) AS LAST\_MONTH\_TWO\_FISCAL\_QUARTERS\_AGO\_START, BUILTIN.RELATIVE\_RANGES( 'LMFQBL', 'END' ) AS LAST\_MONTH\_TWO\_FISCAL\_QUARTERS\_AGO\_END, BUILTIN.RELATIVE\_RANGES( 'LMFYBL', 'START' ) AS LAST\_MONTH\_TWO\_FISCAL\_YEARS\_AGO\_START, BUILTIN.RELATIVE\_RANGES( 'LMFYBL', 'END' ) AS LAST\_MONTH\_TWO\_FISCAL\_YEARS\_AGO\_END, BUILTIN.RELATIVE\_RANGES( 'LRH', 'START' ) AS LAST\_ROLLING\_HALF\_START, BUILTIN.RELATIVE\_RANGES( 'LRH', 'END' ) AS LAST\_ROLLING\_HALF\_END, BUILTIN.RELATIVE\_RANGES( 'LRQ', 'START' ) AS LAST\_ROLLING\_QUARTER\_START, BUILTIN.RELATIVE\_RANGES( 'LRQ', 'END' ) AS LAST\_ROLLING\_QUARTER\_END, BUILTIN.RELATIVE\_RANGES( 'LRY', 'START' ) AS LAST\_ROLLING\_YEAR\_START, BUILTIN.RELATIVE\_RANGES( 'LRY', 'END' ) AS LAST\_ROLLING\_YEAR\_END, BUILTIN.RELATIVE\_RANGES( 'LW', 'START' ) AS LAST\_WEEK\_START, BUILTIN.RELATIVE\_RANGES( 'LW', 'END' ) AS LAST\_WEEK\_END, BUILTIN.RELATIVE\_RANGES( 'LWTD', 'START' ) AS LAST\_WEEK\_TO\_DATE\_START, BUILTIN.RELATIVE\_RANGES( 'LWTD', 'END' ) AS LAST\_WEEK\_TO\_DATE\_END, BUILTIN.RELATIVE\_RANGES( 'LY', 'START' ) AS LAST\_YEAR\_START, BUILTIN.RELATIVE\_RANGES( 'LY', 'END' ) AS LAST\_YEAR\_END, BUILTIN.RELATIVE\_RANGES( 'LYTD', 'START' ) AS LAST\_YEAR\_TO\_DATE\_START, BUILTIN.RELATIVE\_RANGES( 'LYTD', 'END' ) AS LAST\_YEAR\_TO\_DATE\_END, BUILTIN.RELATIVE\_RANGES( 'MAN', 'START' ) AS MONTH\_AFTER\_NEXT\_START, BUILTIN.RELATIVE\_RANGES( 'MAN', 'END' ) AS MONTH\_AFTER\_NEXT\_END, BUILTIN.RELATIVE\_RANGES( 'MANTD', 'START' ) AS MONTH\_AFTER\_NEXT\_TO\_DATE\_START, BUILTIN.RELATIVE\_RANGES( 'MANTD', 'END' ) AS MONTH\_AFTER\_NEXT\_TO\_DATE\_END, BUILTIN.RELATIVE\_RANGES( 'MBL', 'START' ) AS MONTH\_BEFORE\_LAST\_START, BUILTIN.RELATIVE\_RANGES( 'MBL', 'END' ) AS MONTH\_BEFORE\_LAST\_END, BUILTIN.RELATIVE\_RANGES( 'MBLTD', 'START' ) AS MONTH\_BEFORE\_LAST\_TO\_DATE\_START, BUILTIN.RELATIVE\_RANGES( 'MBLTD', 'END' ) AS MONTH\_BEFORE\_LAST\_TO\_DATE\_END, BUILTIN.RELATIVE\_RANGES( 'NBW', 'START' ) AS NEXT\_BUSINESS\_WEEK\_START, BUILTIN.RELATIVE\_RANGES( 'NBW', 'END' ) AS NEXT\_BUSINESS\_WEEK\_END, BUILTIN.RELATIVE\_RANGES( 'NFH', 'START' ) AS NEXT\_FISCAL\_HALF\_START, BUILTIN.RELATIVE\_RANGES( 'NFH', 'END' ) AS NEXT\_FISCAL\_HALF\_END, BUILTIN.RELATIVE\_RANGES( 'NFQ', 'START' ) AS NEXT\_FISCAL\_QUARTER\_START, BUILTIN.RELATIVE\_RANGES( 'NFQ', 'END' ) AS NEXT\_FISCAL\_QUARTER\_END, BUILTIN.RELATIVE\_RANGES( 'NFY', 'START' ) AS NEXT\_FISCAL\_YEAR\_START, BUILTIN.RELATIVE\_RANGES( 'NFY', 'END' ) AS NEXT\_FISCAL\_YEAR\_END, BUILTIN.RELATIVE\_RANGES( 'N4W', 'START' ) AS NEXT\_FOUR\_WEEKS\_START, BUILTIN.RELATIVE\_RANGES( 'N4W', 'END' ) AS NEXT\_FOUR\_WEEKS\_END, BUILTIN.RELATIVE\_RANGES( 'NM', 'START' ) AS NEXT\_MONTH\_START, BUILTIN.RELATIVE\_RANGES( 'NM', 'END' ) AS NEXT\_MONTH\_END, BUILTIN.RELATIVE\_RANGES( 'NOH', 'START' ) AS NEXT\_ONE\_HALF\_START, BUILTIN.RELATIVE\_RANGES( 'NOH', 'END' ) AS NEXT\_ONE\_HALF\_END, BUILTIN.RELATIVE\_RANGES( 'NOM', 'START' ) AS NEXT\_ONE\_MONTH\_START, BUILTIN.RELATIVE\_RANGES( 'NOM', 'END' ) AS NEXT\_ONE\_MONTH\_END, BUILTIN.RELATIVE\_RANGES( 'NOQ', 'START' ) AS NEXT\_ONE\_QUARTER\_START, BUILTIN.RELATIVE\_RANGES( 'NOQ', 'END' ) AS NEXT\_ONE\_QUARTER\_END, BUILTIN.RELATIVE\_RANGES( 'NOW', 'START' ) AS NEXT\_ONE\_WEEK\_START, BUILTIN.RELATIVE\_RANGES( 'NOW', 'END' ) AS NEXT\_ONE\_WEEK\_END, BUILTIN.RELATIVE\_RANGES( 'NOY', 'START' ) AS NEXT\_ONE\_YEAR\_START, BUILTIN.RELATIVE\_RANGES( 'NOY', 'END' ) AS NEXT\_ONE\_YEAR\_END, BUILTIN.RELATIVE\_RANGES( 'NW', 'START' ) AS NEXT\_WEEK\_START, BUILTIN.RELATIVE\_RANGES( 'NW', 'END' ) AS NEXT\_WEEK\_END, BUILTIN.RELATIVE\_RANGES( 'DAGO90', 'START' ) AS NINETY\_DAYS\_AGO\_START, BUILTIN.RELATIVE\_RANGES( 'DAGO90', 'END' ) AS NINETY\_DAYS\_AGO\_END, BUILTIN.RELATIVE\_RANGES( 'DFN90', 'START' ) AS NINETY\_DAYS\_FROM\_NOW\_START, BUILTIN.RELATIVE\_RANGES( 'DFN90', 'END' ) AS NINETY\_DAYS\_FROM\_NOW\_END, BUILTIN.RELATIVE\_RANGES( 'OYBL', 'START' ) AS ONE\_YEAR\_BEFORE\_LAST\_START, BUILTIN.RELATIVE\_RANGES( 'OYBL', 'END' ) AS ONE\_YEAR\_BEFORE\_LAST\_END, BUILTIN.RELATIVE\_RANGES( 'PQLFY', 'START' ) AS PREVIOUS\_FISCAL\_QUARTERS\_LAST\_FISCAL\_YEAR\_START, BUILTIN.RELATIVE\_RANGES( 'PQLFY', 'END' ) AS PREVIOUS\_FISCAL\_QUARTERS\_LAST\_FISCAL\_YEAR\_END, BUILTIN.RELATIVE\_RANGES( 'PQTFY', 'START' ) AS PREVIOUS\_FISCAL\_QUARTERS\_THIS\_FISCAL\_YEAR\_START, BUILTIN.RELATIVE\_RANGES( 'PQTFY', 'END' ) AS PREVIOUS\_FISCAL\_QUARTERS\_THIS\_FISCAL\_YEAR\_END, BUILTIN.RELATIVE\_RANGES( 'PMLFH', 'START' ) AS PREVIOUS\_MONTHS\_LAST\_FISCAL\_HALF\_START, BUILTIN.RELATIVE\_RANGES( 'PMLFH', 'END' ) AS PREVIOUS\_MONTHS\_LAST\_FISCAL\_HALF\_END, BUILTIN.RELATIVE\_RANGES( 'PMLFQ', 'START' ) AS PREVIOUS\_MONTHS\_LAST\_FISCAL\_QUARTER\_START, BUILTIN.RELATIVE\_RANGES( 'PMLFQ', 'END' ) AS PREVIOUS\_MONTHS\_LAST\_FISCAL\_QUARTER\_END, BUILTIN.RELATIVE\_RANGES( 'PMLFY', 'START' ) AS PREVIOUS\_MONTHS\_LAST\_FISCAL\_YEAR\_START, BUILTIN.RELATIVE\_RANGES( 'PMLFY', 'END' ) AS PREVIOUS\_MONTHS\_LAST\_FISCAL\_YEAR\_END, BUILTIN.RELATIVE\_RANGES( 'PMSFHLFY', 'START' ) AS PREVIOUS\_MONTHS\_SAME\_FISCAL\_HALF\_LAST\_FISCAL\_YEAR\_START, BUILTIN.RELATIVE\_RANGES( 'PMSFHLFY', 'END' ) AS PREVIOUS\_MONTHS\_SAME\_FISCAL\_HALF\_LAST\_FISCAL\_YEAR\_END, BUILTIN.RELATIVE\_RANGES( 'PMSFQLFY', 'START' ) AS PREVIOUS\_MONTHS\_SAME\_FISCAL\_QUARTER\_LAST\_FISCAL\_YEAR\_START, BUILTIN.RELATIVE\_RANGES( 'PMSFQLFY', 'END' ) AS PREVIOUS\_MONTHS\_SAME\_FISCAL\_QUARTER\_LAST\_FISCAL\_YEAR\_END, BUILTIN.RELATIVE\_RANGES( 'PMTFH', 'START' ) AS PREVIOUS\_MONTHS\_THIS\_FISCAL\_HALF\_START, BUILTIN.RELATIVE\_RANGES( 'PMTFH', 'END' ) AS PREVIOUS\_MONTHS\_THIS\_FISCAL\_HALF\_END, BUILTIN.RELATIVE\_RANGES( 'PMTFQ', 'START' ) AS PREVIOUS\_MONTHS\_THIS\_FISCAL\_QUARTER\_START, BUILTIN.RELATIVE\_RANGES( 'PMTFQ', 'END' ) AS PREVIOUS\_MONTHS\_THIS\_FISCAL\_QUARTER\_END, BUILTIN.RELATIVE\_RANGES( 'PMTFY', 'START' ) AS PREVIOUS\_MONTHS\_THIS\_FISCAL\_YEAR\_START, BUILTIN.RELATIVE\_RANGES( 'PMTFY', 'END' ) AS PREVIOUS\_MONTHS\_THIS\_FISCAL\_YEAR\_END, BUILTIN.RELATIVE\_RANGES( 'OD', 'START' ) AS PREVIOUS\_ONE\_DAY\_START, BUILTIN.RELATIVE\_RANGES( 'OD', 'END' ) AS PREVIOUS\_ONE\_DAY\_END, BUILTIN.RELATIVE\_RANGES( 'OH', 'START' ) AS PREVIOUS\_ONE\_HALF\_START, BUILTIN.RELATIVE\_RANGES( 'OH', 'END' ) AS PREVIOUS\_ONE\_HALF\_END, BUILTIN.RELATIVE\_RANGES( 'OM', 'START' ) AS PREVIOUS\_ONE\_MONTH\_START, BUILTIN.RELATIVE\_RANGES( 'OM', 'END' ) AS PREVIOUS\_ONE\_MONTH\_END, BUILTIN.RELATIVE\_RANGES( 'OQ', 'START' ) AS PREVIOUS\_ONE\_QUARTER\_START, BUILTIN.RELATIVE\_RANGES( 'OQ', 'END' ) AS PREVIOUS\_ONE\_QUARTER\_END, BUILTIN.RELATIVE\_RANGES( 'OW', 'START' ) AS PREVIOUS\_ONE\_WEEK\_START, BUILTIN.RELATIVE\_RANGES( 'OW', 'END' ) AS PREVIOUS\_ONE\_WEEK\_END, BUILTIN.RELATIVE\_RANGES( 'OY', 'START' ) AS PREVIOUS\_ONE\_YEAR\_START, BUILTIN.RELATIVE\_RANGES( 'OY', 'END' ) AS PREVIOUS\_ONE\_YEAR\_END, BUILTIN.RELATIVE\_RANGES( 'PRH', 'START' ) AS PREVIOUS\_ROLLING\_HALF\_START, BUILTIN.RELATIVE\_RANGES( 'PRH', 'END' ) AS PREVIOUS\_ROLLING\_HALF\_END, BUILTIN.RELATIVE\_RANGES( 'PRQ', 'START' ) AS PREVIOUS\_ROLLING\_QUARTER\_START, BUILTIN.RELATIVE\_RANGES( 'PRQ', 'END' ) AS PREVIOUS\_ROLLING\_QUARTER\_END, BUILTIN.RELATIVE\_RANGES( 'PRY', 'START' ) AS PREVIOUS\_ROLLING\_YEAR\_START, BUILTIN.RELATIVE\_RANGES( 'PRY', 'END' ) AS PREVIOUS\_ROLLING\_YEAR\_END, BUILTIN.RELATIVE\_RANGES( 'SDFQBL', 'START' ) AS SAME\_DAY\_FISCAL\_QUARTER\_BEFORE\_LAST\_START, BUILTIN.RELATIVE\_RANGES( 'SDFQBL', 'END' ) AS SAME\_DAY\_FISCAL\_QUARTER\_BEFORE\_LAST\_END, BUILTIN.RELATIVE\_RANGES( 'SDFYBL', 'START' ) AS SAME\_DAY\_FISCAL\_YEAR\_BEFORE\_LAST\_START, BUILTIN.RELATIVE\_RANGES( 'SDFYBL', 'END' ) AS SAME\_DAY\_FISCAL\_YEAR\_BEFORE\_LAST\_END, BUILTIN.RELATIVE\_RANGES( 'SDLFQ', 'START' ) AS SAME\_DAY\_LAST\_FISCAL\_QUARTER\_START, BUILTIN.RELATIVE\_RANGES( 'SDLFQ', 'END' ) AS SAME\_DAY\_LAST\_FISCAL\_QUARTER\_END, BUILTIN.RELATIVE\_RANGES( 'SDLFY', 'START' ) AS SAME\_DAY\_LAST\_FISCAL\_YEAR\_START, BUILTIN.RELATIVE\_RANGES( 'SDLFY', 'END' ) AS SAME\_DAY\_LAST\_FISCAL\_YEAR\_END, BUILTIN.RELATIVE\_RANGES( 'SDLM', 'START' ) AS SAME\_DAY\_LAST\_MONTH\_START, BUILTIN.RELATIVE\_RANGES( 'SDLM', 'END' ) AS SAME\_DAY\_LAST\_MONTH\_END, BUILTIN.RELATIVE\_RANGES( 'SDLW', 'START' ) AS SAME\_DAY\_LAST\_WEEK\_START, BUILTIN.RELATIVE\_RANGES( 'SDLW', 'END' ) AS SAME\_DAY\_LAST\_WEEK\_END, BUILTIN.RELATIVE\_RANGES( 'SDMBL', 'START' ) AS SAME\_DAY\_MONTH\_BEFORE\_LAST\_START, BUILTIN.RELATIVE\_RANGES( 'SDMBL', 'END' ) AS SAME\_DAY\_MONTH\_BEFORE\_LAST\_END, BUILTIN.RELATIVE\_RANGES( 'SDWBL', 'START' ) AS SAME\_DAY\_WEEK\_BEFORE\_LAST\_START, BUILTIN.RELATIVE\_RANGES( 'SDWBL', 'END' ) AS SAME\_DAY\_WEEK\_BEFORE\_LAST\_END, BUILTIN.RELATIVE\_RANGES( 'SFHLFY', 'START' ) AS SAME\_FISCAL\_HALF\_LAST\_FISCAL\_YEAR\_START, BUILTIN.RELATIVE\_RANGES( 'SFHLFY', 'END' ) AS SAME\_FISCAL\_HALF\_LAST\_FISCAL\_YEAR\_END, BUILTIN.RELATIVE\_RANGES( 'SFHLFYTD', 'START' ) AS SAME\_FISCAL\_HALF\_LAST\_FISCAL\_YEAR\_TO\_DATE\_START, BUILTIN.RELATIVE\_RANGES( 'SFHLFYTD', 'END' ) AS SAME\_FISCAL\_HALF\_LAST\_FISCAL\_YEAR\_TO\_DATE\_END, BUILTIN.RELATIVE\_RANGES( 'SFQFYBL', 'START' ) AS SAME\_FISCAL\_QUARTER\_FISCAL\_YEAR\_BEFORE\_LAST\_START, BUILTIN.RELATIVE\_RANGES( 'SFQFYBL', 'END' ) AS SAME\_FISCAL\_QUARTER\_FISCAL\_YEAR\_BEFORE\_LAST\_END, BUILTIN.RELATIVE\_RANGES( 'SFQLFY', 'START' ) AS SAME\_FISCAL\_QUARTER\_LAST\_FISCAL\_YEAR\_START, BUILTIN.RELATIVE\_RANGES( 'SFQLFY', 'END' ) AS SAME\_FISCAL\_QUARTER\_LAST\_FISCAL\_YEAR\_END, BUILTIN.RELATIVE\_RANGES( 'SFQLFYTD', 'START' ) AS SAME\_FISCAL\_QUARTER\_LAST\_FISCAL\_YEAR\_TO\_DATE\_START, BUILTIN.RELATIVE\_RANGES( 'SFQLFYTD', 'END' ) AS SAME\_FISCAL\_QUARTER\_LAST\_FISCAL\_YEAR\_TO\_DATE\_END, BUILTIN.RELATIVE\_RANGES( 'SMFQBL', 'START' ) AS SAME\_MONTH\_FISCAL\_QUARTER\_BEFORE\_LAST\_START, BUILTIN.RELATIVE\_RANGES( 'SMFQBL', 'END' ) AS SAME\_MONTH\_FISCAL\_QUARTER\_BEFORE\_LAST\_END, BUILTIN.RELATIVE\_RANGES( 'SMFYBL', 'START' ) AS SAME\_MONTH\_FISCAL\_YEAR\_BEFORE\_LAST\_START, BUILTIN.RELATIVE\_RANGES( 'SMFYBL', 'END' ) AS SAME\_MONTH\_FISCAL\_YEAR\_BEFORE\_LAST\_END, BUILTIN.RELATIVE\_RANGES( 'SMLFQ', 'START' ) AS SAME\_MONTH\_LAST\_FISCAL\_QUARTER\_START, BUILTIN.RELATIVE\_RANGES( 'SMLFQ', 'END' ) AS SAME\_MONTH\_LAST\_FISCAL\_QUARTER\_END, BUILTIN.RELATIVE\_RANGES( 'SMLFQTD', 'START' ) AS SAME\_MONTH\_LAST\_FISCAL\_QUARTER\_TO\_DATE\_START, BUILTIN.RELATIVE\_RANGES( 'SMLFQTD', 'END' ) AS SAME\_MONTH\_LAST\_FISCAL\_QUARTER\_TO\_DATE\_END, BUILTIN.RELATIVE\_RANGES( 'SMLFY', 'START' ) AS SAME\_MONTH\_LAST\_FISCAL\_YEAR\_START, BUILTIN.RELATIVE\_RANGES( 'SMLFY', 'END' ) AS SAME\_MONTH\_LAST\_FISCAL\_YEAR\_END, BUILTIN.RELATIVE\_RANGES( 'SMLFYTD', 'START' ) AS SAME\_MONTH\_LAST\_FISCAL\_YEAR\_TO\_DATE\_START, BUILTIN.RELATIVE\_RANGES( 'SMLFYTD', 'END' ) AS SAME\_MONTH\_LAST\_FISCAL\_YEAR\_TO\_DATE\_END, BUILTIN.RELATIVE\_RANGES( 'SWFYBL', 'START' ) AS SAME\_WEEK\_FISCAL\_YEAR\_BEFORE\_LAST\_START, BUILTIN.RELATIVE\_RANGES( 'SWFYBL', 'END' ) AS SAME\_WEEK\_FISCAL\_YEAR\_BEFORE\_LAST\_END, BUILTIN.RELATIVE\_RANGES( 'SWLFY', 'START' ) AS SAME\_WEEK\_LAST\_FISCAL\_YEAR\_START, BUILTIN.RELATIVE\_RANGES( 'SWLFY', 'END' ) AS SAME\_WEEK\_LAST\_FISCAL\_YEAR\_END, BUILTIN.RELATIVE\_RANGES( 'DAGO60', 'START' ) AS SIXTY\_DAYS\_AGO\_START, BUILTIN.RELATIVE\_RANGES( 'DAGO60', 'END' ) AS SIXTY\_DAYS\_AGO\_END, BUILTIN.RELATIVE\_RANGES( 'DFN60', 'START' ) AS SIXTY\_DAYS\_FROM\_NOW\_START, BUILTIN.RELATIVE\_RANGES( 'DFN60', 'END' ) AS SIXTY\_DAYS\_FROM\_NOW\_END, BUILTIN.RELATIVE\_RANGES( 'DAGO10', 'START' ) AS TEN\_DAYS\_AGO\_START, BUILTIN.RELATIVE\_RANGES( 'DAGO10', 'END' ) AS TEN\_DAYS\_AGO\_END, BUILTIN.RELATIVE\_RANGES( 'DFN10', 'START' ) AS TEN\_DAYS\_FROM\_NOW\_START, BUILTIN.RELATIVE\_RANGES( 'DFN10', 'END' ) AS TEN\_DAYS\_FROM\_NOW\_END, BUILTIN.RELATIVE\_RANGES( 'DAGO30', 'START' ) AS THIRTY\_DAYS\_AGO\_START, BUILTIN.RELATIVE\_RANGES( 'DAGO30', 'END' ) AS THIRTY\_DAYS\_AGO\_END, BUILTIN.RELATIVE\_RANGES( 'DFN30', 'START' ) AS THIRTY\_DAYS\_FROM\_NOW\_START, BUILTIN.RELATIVE\_RANGES( 'DFN30', 'END' ) AS THIRTY\_DAYS\_FROM\_NOW\_END, BUILTIN.RELATIVE\_RANGES( 'TBW', 'START' ) AS THIS\_BUSINESS\_WEEK\_START, BUILTIN.RELATIVE\_RANGES( 'TBW', 'END' ) AS THIS\_BUSINESS\_WEEK\_END, BUILTIN.RELATIVE\_RANGES( 'TFH', 'START' ) AS THIS\_FISCAL\_HALF\_START, BUILTIN.RELATIVE\_RANGES( 'TFH', 'END' ) AS THIS\_FISCAL\_HALF\_END, BUILTIN.RELATIVE\_RANGES( 'TFHTD', 'START' ) AS THIS\_FISCAL\_HALF\_TO\_DATE\_START, BUILTIN.RELATIVE\_RANGES( 'TFHTD', 'END' ) AS THIS\_FISCAL\_HALF\_TO\_DATE\_END, BUILTIN.RELATIVE\_RANGES( 'TFQ', 'START' ) AS THIS\_FISCAL\_QUARTER\_START, BUILTIN.RELATIVE\_RANGES( 'TFQ', 'END' ) AS THIS\_FISCAL\_QUARTER\_END, BUILTIN.RELATIVE\_RANGES( 'TFQTD', 'START' ) AS THIS\_FISCAL\_QUARTER\_TO\_DATE\_START, BUILTIN.RELATIVE\_RANGES( 'TFQTD', 'END' ) AS THIS\_FISCAL\_QUARTER\_TO\_DATE\_END, BUILTIN.RELATIVE\_RANGES( 'TFY', 'START' ) AS THIS\_FISCAL\_YEAR\_START, BUILTIN.RELATIVE\_RANGES( 'TFY', 'END' ) AS THIS\_FISCAL\_YEAR\_END, BUILTIN.RELATIVE\_RANGES( 'TFYTD', 'START' ) AS THIS\_FISCAL\_YEAR\_TO\_DATE\_START, BUILTIN.RELATIVE\_RANGES( 'TFYTD', 'END' ) AS THIS\_FISCAL\_YEAR\_TO\_DATE\_END, BUILTIN.RELATIVE\_RANGES( 'TM', 'START' ) AS THIS\_MONTH\_START, BUILTIN.RELATIVE\_RANGES( 'TM', 'END' ) AS THIS\_MONTH\_END, BUILTIN.RELATIVE\_RANGES( 'TMTD', 'START' ) AS THIS\_MONTH\_TO\_DATE\_START, BUILTIN.RELATIVE\_RANGES( 'TMTD', 'END' ) AS THIS\_MONTH\_TO\_DATE\_END, BUILTIN.RELATIVE\_RANGES( 'TRH', 'START' ) AS THIS\_ROLLING\_HALF\_START, BUILTIN.RELATIVE\_RANGES( 'TRH', 'END' ) AS THIS\_ROLLING\_HALF\_END, BUILTIN.RELATIVE\_RANGES( 'TRQ', 'START' ) AS THIS\_ROLLING\_QUARTER\_START, BUILTIN.RELATIVE\_RANGES( 'TRQ', 'END' ) AS THIS\_ROLLING\_QUARTER\_END, BUILTIN.RELATIVE\_RANGES( 'TRY', 'START' ) AS THIS\_ROLLING\_YEAR\_START, BUILTIN.RELATIVE\_RANGES( 'TRY', 'END' ) AS THIS\_ROLLING\_YEAR\_END, BUILTIN.RELATIVE\_RANGES( 'TW', 'START' ) AS THIS\_WEEK\_START, BUILTIN.RELATIVE\_RANGES( 'TW', 'END' ) AS THIS\_WEEK\_END, BUILTIN.RELATIVE\_RANGES( 'TWTD', 'START' ) AS THIS\_WEEK\_TO\_DATE\_START, BUILTIN.RELATIVE\_RANGES( 'TWTD', 'END' ) AS THIS\_WEEK\_TO\_DATE\_END, BUILTIN.RELATIVE\_RANGES( 'TY', 'START' ) AS THIS\_YEAR\_START, BUILTIN.RELATIVE\_RANGES( 'TY', 'END' ) AS THIS\_YEAR\_END, BUILTIN.RELATIVE\_RANGES( 'TYTD', 'START' ) AS THIS\_YEAR\_TO\_DATE\_START, BUILTIN.RELATIVE\_RANGES( 'TYTD', 'END' ) AS THIS\_YEAR\_TO\_DATE\_END, BUILTIN.RELATIVE\_RANGES( 'DAGO3', 'START' ) AS THREE\_DAYS\_AGO\_START, BUILTIN.RELATIVE\_RANGES( 'DAGO3', 'END' ) AS THREE\_DAYS\_AGO\_END, BUILTIN.RELATIVE\_RANGES( 'DFN3', 'START' ) AS THREE\_DAYS\_FROM\_NOW\_START, BUILTIN.RELATIVE\_RANGES( 'DFN3', 'END' ) AS THREE\_DAYS\_FROM\_NOW\_END, BUILTIN.RELATIVE\_RANGES( 'FQB', 'START' ) AS THREE\_FISCAL\_QUARTERS\_AGO\_START, BUILTIN.RELATIVE\_RANGES( 'FQB', 'END' ) AS THREE\_FISCAL\_QUARTERS\_AGO\_END, BUILTIN.RELATIVE\_RANGES( 'FQBTD', 'START' ) AS THREE\_FISCAL\_QUARTERS\_AGO\_TO\_DATE\_START, BUILTIN.RELATIVE\_RANGES( 'FQBTD', 'END' ) AS THREE\_FISCAL\_QUARTERS\_AGO\_TO\_DATE\_END, BUILTIN.RELATIVE\_RANGES( 'FYB', 'START' ) AS THREE\_FISCAL\_YEARS\_AGO\_START, BUILTIN.RELATIVE\_RANGES( 'FYB', 'END' ) AS THREE\_FISCAL\_YEARS\_AGO\_END, BUILTIN.RELATIVE\_RANGES( 'FYBTD', 'START' ) AS THREE\_FISCAL\_YEARS\_AGO\_TO\_DATE\_START, BUILTIN.RELATIVE\_RANGES( 'FYBTD', 'END' ) AS THREE\_FISCAL\_YEARS\_AGO\_TO\_DATE\_END, BUILTIN.RELATIVE\_RANGES( 'MB', 'START' ) AS THREE\_MONTHS\_AGO\_START, BUILTIN.RELATIVE\_RANGES( 'MB', 'END' ) AS THREE\_MONTHS\_AGO\_END, BUILTIN.RELATIVE\_RANGES( 'MBTD', 'START' ) AS THREE\_MONTHS\_AGO\_TO\_DATE\_START, BUILTIN.RELATIVE\_RANGES( 'MBTD', 'END' ) AS THREE\_MONTHS\_AGO\_TO\_DATE\_END, BUILTIN.RELATIVE\_RANGES( 'TODAY', 'START' ) AS TODAY\_START, BUILTIN.RELATIVE\_RANGES( 'TODAY', 'END' ) AS TODAY\_END, BUILTIN.RELATIVE\_RANGES( 'TODAYTTM', 'START' ) AS TODAY\_TO\_END\_OF\_THIS\_MONTH\_START, BUILTIN.RELATIVE\_RANGES( 'TODAYTTM', 'END' ) AS TODAY\_TO\_END\_OF\_THIS\_MONTH\_END, BUILTIN.RELATIVE\_RANGES( 'TOMORROW', 'START' ) AS TOMORROW\_START, BUILTIN.RELATIVE\_RANGES( 'TOMORROW', 'END' ) AS TOMORROW\_END, BUILTIN.RELATIVE\_RANGES( 'DAGO2', 'START' ) AS TWO\_DAYS\_AGO\_START, BUILTIN.RELATIVE\_RANGES( 'DAGO2', 'END' ) AS TWO\_DAYS\_AGO\_END, BUILTIN.RELATIVE\_RANGES( 'DFN2', 'START' ) AS TWO\_DAYS\_FROM\_NOW\_START, BUILTIN.RELATIVE\_RANGES( 'DFN2', 'END' ) AS TWO\_DAYS\_FROM\_NOW\_END, BUILTIN.RELATIVE\_RANGES( 'WAN', 'START' ) AS WEEK\_AFTER\_NEXT\_START, BUILTIN.RELATIVE\_RANGES( 'WAN', 'END' ) AS WEEK\_AFTER\_NEXT\_END, BUILTIN.RELATIVE\_RANGES( 'WANTD', 'START' ) AS WEEK\_AFTER\_NEXT\_TO\_DATE\_START, BUILTIN.RELATIVE\_RANGES( 'WANTD', 'END' ) AS WEEK\_AFTER\_NEXT\_TO\_DATE\_END, BUILTIN.RELATIVE\_RANGES( 'WBL', 'START' ) AS WEEK\_BEFORE\_LAST\_START, BUILTIN.RELATIVE\_RANGES( 'WBL', 'END' ) AS WEEK\_BEFORE\_LAST\_END, BUILTIN.RELATIVE\_RANGES( 'WBLTD', 'START' ) AS WEEK\_BEFORE\_LAST\_TO\_DATE\_START, BUILTIN.RELATIVE\_RANGES( 'WBLTD', 'END' ) AS WEEK\_BEFORE\_LAST\_TO\_DATE\_END, BUILTIN.RELATIVE\_RANGES( 'YESTERDAY', 'START' ) AS YESTERDAY\_START, BUILTIN.RELATIVE\_RANGES( 'YESTERDAY', 'END' ) AS YESTERDAY\_END FROM DUAL [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Company Features Returns a list of NetSuite features that are avaialble and/or enabled in the account. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-company-features/ SELECT Name, ID, IsAvailable, IsActive FROM CompanyFeatureSetup ORDER BY Name [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Countries Returns a list of countries that NetSuite supports. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-states-countries/ SELECT Country.ID, Country.Name, Country.Edition, Country.Nationality FROM Country ORDER BY Country.Name [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Currencies Returns a list of currencies, with symbols, latest exchange rates, etc. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-currency-exchange-rates/ SELECT Symbol, Name, ExchangeRate, DisplaySymbol, SymbolPlacement, CurrencyPrecision, IsBaseCurrency, TO\_CHAR( CurrencyRate.LastModifiedDate, 'YYYY-MM-DD HH:MI:SS' ) AS LastModifiedDate FROM Currency WHERE ( IsInactive = 'F' ) ORDER BY Symbol [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Currency Rates Returns historical currency exchange rates. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-currency-exchange-rates/ SELECT BaseCurrency.Symbol AS BaseSymbol, TransactionCurrency.Symbol AS TransactionSymbol, CurrencyRate.ExchangeRate, TO\_CHAR( CurrencyRate.EffectiveDate, 'YYYY-MM-DD HH:MI:SS' ) AS EffectiveDate, TO\_CHAR( CurrencyRate.LastModifiedDate, 'YYYY-MM-DD HH:MI:SS' ) AS LastModifiedDate FROM CurrencyRate INNER JOIN Currency AS BaseCurrency ON ( BaseCurrency.ID = CurrencyRate.BaseCurrency ) INNER JOIN Currency AS TransactionCurrency ON ( TransactionCurrency.ID = CurrencyRate.TransactionCurrency ) WHERE ( CurrencyRate.EffectiveDate = TO\_DATE( '2021-05-03', 'YYYY-MM-DD' ) ) ORDER BY BaseCurrency.Symbol, TransactionCurrency.Symbol, CurrencyRate.LastModifiedDate DESC [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Custom Fields Returns all custom fields in the account. \-- Contributor: Tim Dietrich (timdietrich@me.com) SELECT scriptid, name, fieldtype, fieldvaluetype, BUILTIN.DF( owner ) AS owner, lastmodifieddate FROM CustomField ORDER BY scriptid [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Custom Lists Returns all custom lists in the account. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Source: https://timdietrich.me/blog/netsuite-suiteql-custom-lists/ SELECT Name, Description, ScriptID, BUILTIN.DF( Owner ) AS Owner, IsOrdered FROM CustomList WHERE ( IsInactive = 'F' ) ORDER BY Name [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Custom Record Types Returns all custom record types in the account. \-- Contributor: Tim Dietrich (timdietrich@me.com) SELECT Name, ScriptID, Description, BUILTIN.DF( Owner ) AS Owner FROM CustomRecordType ORDER BY Name [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Custom Segments Returns all custom segments, including the names of the tables in which the segment values are stored. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-custom-segments/ SELECT CustomSegment.Name AS CustomSegmentName, CustomRecordType.ScriptID AS CustomSegmentTableName, CustomSegment.GLImpact FROM CustomSegment INNER JOIN CustomRecordType ON ( CustomRecordType.InternalID = CustomSegment.RecordType ) WHERE ( CustomSegment.IsInactive = 'F' ) ORDER BY CustomSegment.Name [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Customer Deposits Returns all deposits received from a specific customer. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-customer-deposits-2024/ SELECT Transaction.ID AS Transaction, Transaction.TranID, Transaction.TranDate, BUILTIN.DF( Transaction.Entity ) AS Customer, BUILTIN.DF( TransactionMainLine.CreatedFrom ) AS SalesOrder, Transaction.ForeignTotal, BUILTIN.DF( Transaction.PaymentMethod ) AS PaymentMethod, Transaction.OtherRefNum, BUILTIN.DF( Transaction.Status ) AS Status FROM Transaction INNER JOIN TransactionLine AS TransactionMainLine ON ( TransactionMainLine.Transaction = Transaction.ID ) AND ( TransactionMainLine.MainLine = 'T' ) WHERE ( Transaction.Type = 'CustDep' ) AND ( Transaction.Entity = 999999 ) AND ( Transaction.Voided = 'F' ) ORDER BY Transaction.TranID [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Customer Payments: Application Details Returns the invoices to which a customer payment was applied, including sales order numbers, customer PO numbers, and the amounts applied. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-customer-payment-application/ SELECT Invoice.TranID AS InvoiceID, REPLACE( BUILTIN.DF( InvoiceMainLine.CreatedFrom ), 'Sales Order #', '' ) AS SONumber, Invoice.OtherRefNum AS CustomerPO, PTLL.ForeignAmount AS AmountPaid FROM Transaction AS Payment INNER JOIN TransactionLine AS PaymentLine ON ( PaymentLine.Transaction = Payment.ID ) AND ( PaymentLine.MainLine = 'F' ) INNER JOIN PreviousTransactionLineLink AS PTLL ON ( PTLL.NextDoc = PaymentLine.Transaction ) AND ( PTLL.NextLine = PaymentLine.ID ) AND ( PTLL.LinkType = 'Payment' ) INNER JOIN TransactionLine AS InvoiceLine ON ( InvoiceLine.Transaction = PTLL.PreviousDoc ) AND ( InvoiceLine.ID = PTLL.PreviousLine ) INNER JOIN Transaction AS Invoice ON ( Invoice.ID = InvoiceLine.Transaction ) INNER JOIN TransactionLine AS InvoiceMainLine ON ( InvoiceMainLine.Transaction = Invoice.ID ) AND ( InvoiceMainLine.MainLine = 'T' ) WHERE ( Payment.ID = 999999 ) [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Customer Invoices: By Date Range Returns all customer (accounts receivable) invoices for a given date range, with totals and statuses. \-- Contributor: Tim Dietrich (timdietrich@me.com) SELECT Transaction.ID AS Invoice, Transaction.TranID AS InvoiceNumber, Transaction.TranDate AS InvoiceDate, Transaction.Entity AS Customer, BUILTIN.DF( Transaction.Entity ) AS CustomerName, Transaction.OtherRefNum AS CustomerPONumber, TransactionLine.CreatedFrom AS SalesOrder, BUILTIN.DF( TransactionLine.CreatedFrom ) AS SONumber, Transaction.Employee AS SalesRep, BUILTIN.DF( Transaction.Employee ) AS SalesRepName, Transaction.ForeignTotal AS TotalAmount, REPLACE( BUILTIN.DF( Transaction.Status ), 'Invoice : ', '' ) AS Status, Transaction.ForeignAmountUnpaid AS BalanceDue, Transaction.DueDate FROM Transaction INNER JOIN TransactionLine ON ( TransactionLine.Transaction = Transaction.ID ) AND ( TransactionLine.MainLine = 'T' ) LEFT OUTER JOIN Transaction AS SalesOrder ON ( SalesOrder.ID = TransactionLine.CreatedFrom ) WHERE ( Transaction.Type = 'CustInvc' ) AND ( Transaction.TranDate >= BUILTIN.RELATIVE\_RANGES( 'DAGO30', 'START' ) ) ORDER BY Transaction.TranID DESC [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Customers: Billing Addresses Returns a list of customers and their default billto addresses. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-customer-accounts-receivable-queries/ ELECT Customer.EntityID AS ID, Customer.CompanyName AS Name, Customer.CreditLimit, Customer.OnCreditHold, BUILTIN.DF( Customer.Terms ) AS PaymentTerms, BUILTIN.DF( Customer.SalesRep ) AS SalesRep, Customer.BalanceSearch AS BalanceTotal, Customer.OverdueBalanceSearch AS BalancePastDue, BillToAddress.Addressee AS BillToAddressee, BillToAddress.Addr1 As BillToAddress1, BillToAddress.Addr2 As BillToAddress2, BillToAddress.Addr3 As BillToAddress3, BillToAddress.City As BillToCity, BillToAddress.State As BillToState, BillToAddress.Zip As BillToZip, BillToAddress.Country As BillToCountry, BillToAddress.Attention As BillToAttention FROM Customer LEFT OUTER JOIN EntityAddressbook AS DefaultBilling ON ( DefaultBilling.Entity = Customer.ID ) AND ( DefaultBilling.defaultbilling = 'T' ) LEFT OUTER JOIN EntityAddress AS BillToAddress ON ( BillToAddress.nkey = DefaultBilling.AddressBookAddress ) WHERE ( Customer.IsInactive = 'F' ) AND ( Customer.OverdueBalanceSearch > 0 ) ORDER BY Customer.EntityID [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Customers: Transaction Ledgers Used for generatin a transaction ledger for a specified customer. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-customer-transaction-ledgers/ SELECT Transaction.TranDate, Transaction.ID AS Transaction, BUILTIN.DF( Transaction.Type ) AS Type, Transaction.TranID, Transaction.OtherRefNum, Transaction.ForeignTotal, BUILTIN.DF( Transaction.Currency ) AS Currency, REPLACE( BUILTIN.DF( Transaction.Status ), BUILTIN.DF( Transaction.Type ) || ' : ', '' ) AS Status, BUILTIN.DF( Mainline.CreatedFrom ) AS CreatedFrom FROM Transaction INNER JOIN TransactionLine AS Mainline ON ( Mainline.Transaction = Transaction.ID ) AND ( Mainline.MainLine = 'T' ) WHERE ( Transaction.Entity = 237 ) AND ( Transaction.Void = 'F' ) AND ( Transaction.Voided = 'F' ) ORDER BY Transaction.TranDate DESC, Transaction.TranID DESC [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Dates and Times: Creation Examples Shows how you can create dates and timestamps based on strings. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Reference: https://timdietrich.me/blog/netsuite-suiteql-dates-times/ SELECT TO\_DATE( '2020-01-01', 'YYYY-MM-DD' ) AS AdHocDate, -- Note: Wrapping this in "TO\_CHAR" so that you can see that the time portion of the timestamp has been set! TO\_CHAR ( TO\_DATE( '2020-01-01 09:10:11', 'YYYY-MM-DD HH:MI:SS' ), 'YYYY-MM-DD HH:MI:SS' ) AS AdHocTimestamp FROM Dual [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Customers: With Overdue Balances Returns customers that have overdue balances. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-customer-balances/ SELECT Customer.ID, Customer.CompanyName, Customer.EntityID, BUILTIN.DF( Customer.Terms ) AS Terms, Customer.OnCreditHold, Customer.CreditLimit, Customer.BalanceSearch, Customer.OverdueBalanceSearch, Customer.UnbilledOrdersSearch, BUILTIN.DF( Customer.SalesRep ) AS SalesRep FROM Customer WHERE ( OverdueBalanceSearch > 0 ) ORDER BY Customer.CompanyName [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Deleted Records Returns information about records that have been deleted. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-query-deleted-records/ SELECT TO\_CHAR ( DeletedDate, 'DS TS' ) AS DeletedDate, Type AS RecordType, RecordID, BUILTIN.DF( DeletedBy ) AS DeletedBy, BUILTIN.DF( Context ) AS Context FROM DeletedRecord WHERE ( DeletedDate >= TO\_DATE ( TO\_CHAR ( SYSDATE, 'YYYY-MM-DD' ), 'YYYY-MM-DD' ) ) [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Dates and Times: Format Models Returns examples of datetime format elements. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-dates-times/ SELECT TO\_CHAR ( SYSDATE, 'DS TS' ) AS CurrentTimeServer, TO\_CHAR ( CURRENT\_DATE, 'DS TS' ) AS CurrentTimeUser, TO\_CHAR ( SYSDATE, 'YYYY-MM-DD HH:MI:SS' ) AS CurrentSystemTimestamp, TO\_CHAR ( SYSDATE, 'SSSSS' ) AS SecondsSinceMidnight, TO\_CHAR ( SYSDATE, 'RM' ) AS MonthRoman, TO\_CHAR ( SYSDATE, 'MM' ) AS CurrentMonth, TO\_CHAR ( SYSDATE, 'MONTH' ) AS CurrentMonthName, TO\_CHAR ( SYSDATE, 'DD' ) AS CurrentDay, TO\_CHAR ( SYSDATE, 'DAY' ) AS CurrentDayName, TO\_CHAR ( SYSDATE, 'YYYY' ) AS CurrentYear, TO\_CHAR ( SYSDATE, 'YEAR' ) AS CurrentYearName, TO\_CHAR ( SYSDATE, 'Q' ) AS CurrentQuarter, TO\_CHAR ( SYSDATE, 'WW' ) AS WeekNumber FROM Dual [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Employees: Emergency Contacts Returns emergency contact information for employees. SELECT Employee.ID AS EmployeeID, Employee.FirstName, Employee.LastName, Employee.Email, EmployeeEmergencyContact.Contact, EmployeeEmergencyContact.Relationship, EmployeeEmergencyContact.Address, EmployeeEmergencyContact.Phone FROM Employee LEFT OUTER JOIN EmployeeEmergencyContact ON ( EmployeeEmergencyContact.Employee = Employee.ID ) ORDER BY Employee.LastName, Employee.FirstName [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Employees: No Recent Logins Returns employees that have not logged in recently. SELECT Employee.LastName, Employee.FirstName FROM Employee WHERE ( Employee.GiveAccess = 'T' ) AND ( Employee.IsInactive = 'F' ) AND ( Employee.ID NOT IN ( SELECT DISTINCT LoginAudit.User FROM LoginAudit WHERE ( LoginAudit.Date >= ( SYSDATE - 90 ) ) ) ) ORDER BY Employee.LastName, Employee.FirstName [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Employees: Last Logins Returns all employees that have NetSuite access, and the date/time that they last logged in. \-- Contributor: Tim Dietrich (timdietrich@me.com) SELECT Employee.LastName, Employee.FirstName, Employee.IsInactive, Employee.GiveAccess, (SELECT MAX ( TO\_CHAR ( LoginAudit.Date, 'YYYY-MM-DD HH:MI:SS' ) ) FROM LoginAudit WHERE LoginAudit.User = Employee.ID ) AS LastLogin FROM Employee.Employee WHERE Employee.GiveAccess = 'T' ORDER BY Employee.LastName, Employee.FirstName [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Employees: With Edit, View, Email URLs Returns all employees, with edit, view, and email URLs in the results. \-- Contributor: Tim Dietrich (timdietrich@me.com) SELECT '[Edit](/app/common/entity/employee.nl?id=' || Employee.ID || '&e=T) ' || ' | ' || '[View](/app/common/entity/employee.nl?id=' || Employee.ID || ') ' AS Options, LastName, FirstName, Title, '[' || Email || '](mailto:' || Email ||') ' AS Email FROM Employee ORDER BY LastName, FirstName [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Employees: Roles and Permissions Returns all employees, their assigned roles, and the permissions granted. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-employees-supervisors-roles-permissions/ SELECT Employee.ID AS EmployeeID, Employee.LastName, Employee.FirstName, Employee.Title, Employee.Email, Supervisor.LastName AS SupervisorLastName, Supervisor.FirstName AS SupervisorFirstName, Supervisor.Title AS SupervisorTitle, Supervisor.Email AS SupervisorEmail, Role.Name AS RoleName, RolePermissions.Name AS PermissionName, BUILTIN.DF( RolePermissions.PermLevel ) AS PermissionLevel FROM Employee LEFT OUTER JOIN Employee AS Supervisor ON ( Supervisor.ID = Employee.Supervisor ) INNER JOIN EmployeeRolesForSearch ON ( EmployeeRolesForSearch.Entity = Employee.ID ) INNER JOIN Role ON ( Role.ID = EmployeeRolesForSearch.Role ) AND ( Role.IsInactive = 'F' ) INNER JOIN RolePermissions ON ( RolePermissions.Role = Role.ID ) WHERE ( Employee.IsInactive = 'F' ) AND ( Employee.GiveAccess = 'T' ) ORDER BY Employee.LastName, Employee.FirstName [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Entities: Types Returns all entity types, with BUILTIN.DF and BUILTIN.CF values. \-- Contributor: Tim Dietrich (timdietrich@me.com) SELECT DISTINCT Type, BUILTIN.DF( Type ) AS DFType, BUILTIN.CF( Type ) AS CFType FROM Entity ORDER BY Type [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### File Cabinet: Files By Name Returns information about a file stored in the File Cabinet, based on a specified file name. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-file-cabinet/ SELECT File.Name, File.CreatedDate, File.LastModifiedDate, File.FileType, File.FileSize, File.URL, MediaItemFolder.AppFolder FROM File INNER JOIN MediaItemFolder ON (MediaItemFolder.id = File.Folder) WHERE ( File.Name LIKE '%restlet%' ) [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### File Cabinet: Top Level Folders Returns a list of folders in the root of the File Cabinet. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-file-cabinet/ SELECT \* FROM MediaItemFolder WHERE ( IsTopLevel = 'T' ) [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### General Ledger Transactions by Account and Date Range Returns all of the transactions that were posted to a specified general ledger account in a specified date range. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-transactions-posted-to-gl-account/ SELECT Transaction.TranDate, Transaction.TranID, BUILTIN.DF( Transaction.Entity ) AS Entity, TransactionAccountingLine.Debit, TransactionAccountingLine.Credit FROM Transaction INNER JOIN TransactionAccountingLine ON ( TransactionAccountingLine.Transaction = Transaction.ID ) INNER JOIN Account ON ( Account.ID = TransactionAccountingLine.Account ) WHERE ( Transaction.TranDate >= BUILTIN.RELATIVE\_RANGES( 'DAGO30', 'START' ) ) AND ( Transaction.Posting = 'T' ) AND ( TransactionAccountingLine.Posting = 'T' ) AND ( ( TransactionAccountingLine.Debit IS NOT NULL ) OR ( TransactionAccountingLine.Credit IS NOT NULL ) ) AND ( Account.AcctNumber = 7110 ) ORDER BY Transaction.TranDate, Transaction.TranID, TransactionAccountingLine.TransactionLine [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### General Ledger Impact by Transaction Type Returns all transaction types and the general ledger accounts that they post to. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-general-ledger-impact-by-transaction-type/ SELECT BUILTIN.DF( Transaction.Type ) AS TransactionType, BUILTIN.DF( Account.AcctType ) AS AccountType, Account.DisplayNameWithHierarchy AS AccountHierarchy, TransactionAccountingLine.Posting, SUM( TransactionAccountingLine.Debit ) AS Debits, SUM( TransactionAccountingLine.Credit ) AS Credits, SUM( TransactionAccountingLine.Amount ) AS Amount FROM Transaction INNER JOIN TransactionAccountingLine ON ( TransactionAccountingLine.Transaction = Transaction.ID ) INNER JOIN Account ON ( Account.ID = TransactionAccountingLine.Account ) WHERE ( Transaction.TranDate >= BUILTIN.RELATIVE\_RANGES( 'DAGO30', 'START' ) ) GROUP BY BUILTIN.DF( Transaction.Type ), BUILTIN.DF( Account.AcctType ), Account.DisplayNameWithHierarchy, TransactionAccountingLine.Posting ORDER BY TransactionType, AccountType, DisplayNameWithHierarchy, TransactionAccountingLine.Posting [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Hello World An obligatory "Hello World" query in SuiteQL fashion. \-- Contributor: Tim Dietrich (timdietrich@me.com) SELECT 'Hello, world!' AS message FROM DUAL [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Items: Aggregate Information by Location Returns location-specific inventory information for a specified item. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-upsell-items-by-customer/ SELECT BUILTIN.DF( Item ) AS Item, BUILTIN.DF( Location ) AS Location, QuantityOnHand, OnHandValueMLI AS Value, AverageCostMLI AS AverageCost, LastPurchasePriceMli AS LastPurchasePrice, PreferredStockLevel AS PreferredStockLevel, LeadTime AS PurchaseLeadTime, SafetyStockLevel, LeadTimeOffset, QuantityOnOrder, QuantityCommitted, QuantityAvailable, QuantityBackOrdered, QuantityInTransit, QtyInTransitExternal AS QuantityInTransitExternal, LastInvtCountDate AS LastCountDate, NextInvtCountDate AS NextCountDate, InvtCountInterval AS CountInterval, InvtClassification AS Classification, CostingLotSize FROM AggregateItemLocation WHERE Item = 212 ORDER BY Item, Location [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Item Fulfillments: for Specified Sales Order Returns all item fulfillments for a specified Sales Order ID. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Item Fulfillments for a given Sales Order. SELECT SalesOrder.ID AS SalesOrder, SalesOrder.TranID As SalesOrderID, BUILTIN.DF( SalesOrder.Status ) AS SalesOrderStatus, Fulfillment.ID AS Fulfillment, Fulfillment.TranID As FulfillmentID, BUILTIN.DF( Fulfillment.Status ) AS FulfillmentStatus FROM NextTransactionLink AS NTL INNER JOIN Transaction AS SalesOrder ON ( SalesOrder.ID = NTL.PreviousDoc ) INNER JOIN Transaction AS Fulfillment ON ( Fulfillment.ID = NTL.NextDoc ) WHERE ( NTL.LinkType IN ( 'PickPack', 'ShipRcpt' ) ) -- This is the internal ID of the SalesOrder. AND ( NTL.PreviousDoc = 91164478 ) [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Items: Customer Upsells Returns a list of potential upsell items and item categories for a specific customer. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-upsell-items-by-customer/ SELECT BUILTIN.DF( UpsellItem.PurchasedItem ) AS ItemPurchased, BUILTIN.DF( UpsellItem.Item ) AS UpsellItem, ( UpsellItem.CorrrelationFld \* 100 ) AS Corrrelation, UpsellItem.CountFld AS Count, ( SELECT COALESCE( MAX( Price ), 0 ) FROM ItemPrice WHERE ( Item = UpsellItem.Item ) AND ( PriceLevelName = 'VIP Customer' ) AND ( IsInactive = 'F' ) ) AS UnitPrice FROM UpsellItem WHERE UpsellItem.Customer = 237 ORDER BY Corrrelation DESC, Count DESC, ItemPurchased [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### HTTP Requests - Outbound Returns information about outbound HTTP requests being made. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-outbound-http-requests/ SELECT TO\_CHAR( OutboundRequest.Time, 'YYYY-MM-DD hh:mi:ss') AS RequestTime, OutboundRequest.Key, OutboundRequest.RequestID, OutboundRequest.Elapsed, OutboundRequest.Host, OutboundRequest.Port, OutboundRequest.URL, OutboundRequest.StatusCode, OutboundRequest.Error, OutboundRequest.RequestContentType, OutboundRequest.RequestContentLength, OutboundRequest.ResponseContentType, OutboundRequest.ResponseContentLength, OutboundRequest.ScriptDeploymentURL, OutboundRequest.ScriptID, Script.Name AS ScriptName, Script.ScriptType, BUILTIN.DF( Script.Owner ) AS ScriptOwner, MediaItemFolder.Name AS FolderName, File.Name AS FileName, File.CreatedDate AS FileCreatedDate, File.LastModifiedDate AS FileLastModifiedDate FROM OutboundRequest LEFT OUTER JOIN Script ON ( Script.ScriptID = OutboundRequest.ScriptID ) LEFT OUTER JOIN File ON ( File.ID = Script.ScriptFile ) LEFT OUTER JOIN MediaItemFolder ON ( MediaItemFolder.ID = File.Folder ) WHERE OutboundRequest.Time BETWEEN TO\_DATE( '2020-01-01', 'YYYY-MM-DD' ) AND TO\_DATE( '2020-12-31', 'YYYY-MM-DD' ) ORDER BY OutboundRequest.Time DESC [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Items: Customers That Purchased A Specific Item Returns a list of customers that purchased a specified item. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-customers-by-address-and-purchase-history/ SELECT DISTINCT Customer.LastName, Customer.FirstName, Customer.Email, EntityAddress.Addr1, EntityAddress.Addr2, EntityAddress.Addr3, EntityAddress.City, EntityAddress.State, EntityAddress.Zip FROM Transaction INNER JOIN TransactionLine ON ( TransactionLine.Transaction = Transaction.ID ) INNER JOIN Customer ON ( Customer.ID = Transaction.Entity ) INNER JOIN EntityAddress ON ( EntityAddress.nKey = Customer.DefaultShippingAddress ) WHERE ( Transaction.Type = 'SalesOrd' ) AND ( TransactionLine.Item = 8919 ) AND ( Customer.IsInactive = 'F' ) [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Items: Expired or Expiring Soon Returns items that have expired, or are about to expire within a certain number of days. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-serialized-lot-number-expiring-items/ SELECT BUILTIN.DF( InventoryNumber.Item ) AS ItemID, InventoryNumber.InventoryNumber, InventoryNumber.QuantityOnHand, TO\_DATE( InventoryNumber.ExpirationDate ) - TO\_DATE( SYSDATE ) AS ExpirationDays FROM InventoryNumber WHERE InventoryNumber.ExpirationDate <= ( SYSDATE + 30 ) ORDER BY InventoryNumber.ExpirationDate [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Items: Frequently Sold Together Returns a list of items that are frequently sold along with a specified item. This can be used to implement the "cross-selling" sales technique. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-items-frequently-sold-together/ SELECT TOP 10 SalesOrderLine2.Item AS Item, BUILTIN.DF( SalesOrderLine2.Item ) AS ItemName, BUILTIN.DF( SalesOrderLine2.ItemType ) AS ItemType, COUNT(\*) AS TimesSoldTogether, ( SELECT SUM( ItemInventoryBalance.QuantityAvailable ) FROM ItemInventoryBalance WHERE ( ItemInventoryBalance.Item = SalesOrderLine2.Item ) ) AS QuantityAvailable FROM Transaction AS SalesOrder INNER JOIN TransactionLine AS SalesOrderLine1 ON ( SalesOrderLine1.Transaction = SalesOrder.ID ) INNER JOIN TransactionLine AS SalesOrderLine2 ON ( SalesOrderLine2.Transaction = SalesOrderLine1.Transaction ) AND ( SalesOrderLine2.Item <> SalesOrderLine1.Item ) AND ( SalesOrderLine2.ItemType IN ( 'InvtPart', 'NonInvtPart', 'Service' ) ) WHERE ( SalesOrder.Type = 'SalesOrd' ) AND ( SalesOrderLine1.Item = 99999 ) GROUP BY SalesOrderLine2.Item, BUILTIN.DF( SalesOrderLine2.Item ), BUILTIN.DF( SalesOrderLine2.ItemType ) ORDER BY TimesSoldTogether DESC [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Items: Inventory Impact Transactions Returns transactions that impacted an item's inventory level. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Source: https://timdietrich.me/blog/netsuite-suiteql-item-transactions/ SELECT TransactionLine.Transaction AS TransactionID, Transaction.TranDate, Transaction.Type, Transaction.TranID, TransactionLine.Rate, TransactionLine.NetAmount, TransactionLine.Quantity, Entity.AltName FROM TransactionLine INNER JOIN Transaction ON ( Transaction.ID = TransactionLine.Transaction ) LEFT OUTER JOIN Entity ON ( Entity.ID = Transaction.Entity ) WHERE ( TransactionLine.Item = 999999 ) AND ( Transaction.Voided = 'F' ) AND ( TransactionLine.IsInventoryAffecting = 'T' ) ORDER BY Transaction.TranDate, Transaction.TranID [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Items: In Alternate Warehouse Bins Returns items that are in stock and stored in bins that are not the item's primary bin. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-items-in-alternate-bins/ SELECT Item AS ItemD, BUILTIN.DF( Item ) AS ItemName, Bin AS BinID, BUILTIN.DF( Bin ) AS BinName, OnHand FROM ItemBinQuantity WHERE ( PreferredBin = 'F' ) AND ( OnHand > 0 ) ORDER BY Item [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Items: Inventory Analysis Designed for inventory analysis, this query returns active items, various quantities (on hand, committed, available, reorder point, preferred stock level), the preferred vendor, the average resale price and purchase cost, quantities sold (in the last 30 days, 90 days, year, and lifetime), and more. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-inventory-analysis/ SELECT Item.ItemID, BUILTIN.DF( Item.Parent ) AS ParentItem, Item.Description, Item.QuantityOnHand, Item.QuantityCommitted, Item.QuantityAvailable, Item.QuantityOnOrder, Item.QuantityBackOrdered, Item.ReorderPoint, Item.PreferredStockLevel, Item.LeadTime, Vendor.CompanyName AS PreferredVendor, Item.Cost AS PurchasePrice, ROUND( Item.AverageCost, 2 ) AS AverageCost, ROUND( Item.LastPurchasePrice, 2 ) AS LastPurchasePrice, ( SELECT MIN( Transaction.TranDate ) FROM Transaction INNER JOIN TransactionLine ON ( TransactionLine.Transaction = Transaction.ID ) WHERE ( TransactionLine.Item = Item.ID ) AND ( Transaction.Type = 'SalesOrd' ) ) AS FirstSold, ( SELECT MAX( Transaction.TranDate ) FROM Transaction INNER JOIN TransactionLine ON ( TransactionLine.Transaction = Transaction.ID ) WHERE ( TransactionLine.Item = Item.ID ) AND ( Transaction.Type = 'SalesOrd' ) ) AS LastSold, ( SELECT MIN( Transaction.TranDate ) FROM Transaction INNER JOIN TransactionLine ON ( TransactionLine.Transaction = Transaction.ID ) WHERE ( TransactionLine.Item = Item.ID ) AND ( Transaction.Type = 'PurchOrd' ) ) AS FirstPurchased, ( SELECT MAX( Transaction.TranDate ) FROM Transaction INNER JOIN TransactionLine ON ( TransactionLine.Transaction = Transaction.ID ) WHERE ( TransactionLine.Item = Item.ID ) AND ( Transaction.Type = 'PurchOrd' ) ) AS LastPurchased, ( SELECT ( SUM( TransactionLine.Quantity ) \* -1 ) FROM Transaction INNER JOIN TransactionLine ON ( TransactionLine.Transaction = Transaction.ID ) WHERE ( TransactionLine.Item = Item.ID ) AND ( Transaction.Type = 'SalesOrd' ) AND ( ( TRUNC( SYSDATE ) - Transaction.TranDate ) <= 30 ) ) AS QtySold30Days, ( SELECT ( SUM( TransactionLine.Quantity ) \* -1 ) FROM Transaction INNER JOIN TransactionLine ON ( TransactionLine.Transaction = Transaction.ID ) WHERE ( TransactionLine.Item = Item.ID ) AND ( Transaction.Type = 'SalesOrd' ) AND ( ( TRUNC( SYSDATE ) - Transaction.TranDate ) <= 90 ) ) AS QtySold90Days, ( SELECT ( SUM( TransactionLine.Quantity ) \* -1 ) FROM Transaction INNER JOIN TransactionLine ON ( TransactionLine.Transaction = Transaction.ID ) WHERE ( TransactionLine.Item = Item.ID ) AND ( Transaction.Type = 'SalesOrd' ) AND ( ( TRUNC( SYSDATE ) - Transaction.TranDate ) <= 365 ) ) AS QtySold1Year, ( SELECT ( SUM( TransactionLine.Quantity ) \* -1 ) FROM Transaction INNER JOIN TransactionLine ON ( TransactionLine.Transaction = Transaction.ID ) WHERE ( TransactionLine.Item = Item.ID ) AND ( Transaction.Type = 'SalesOrd' ) ) AS QtySoldLifetime FROM Item LEFT OUTER JOIN ItemVendor ON ( ItemVendor.Item = Item.ID ) AND ( ItemVendor.PreferredVendor = 'T' ) LEFT OUTER JOIN Vendor ON ( Vendor.ID = ItemVendor.Vendor ) WHERE ( Item. ItemType = 'InvtPart' ) AND ( ( Item.MatrixType IS NULL ) OR ( Item.MatrixType = 'CHILD' ) ) AND ( Item.IsInactive = 'F' ) AND ( ( Item.QuantityOnHand > 0 ) OR ( Item.QuantityOnOrder > 0 ) OR ( Item.QuantityBackOrdered > 0 ) ) [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Items: Item Group Prices Returns the price for a specified Item Group. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-item-groups/ SELECT ItemGroup.ItemID, ( SELECT SUM( ( ItemPrice.Price \* ItemMember.Quantity ) ) FROM ItemMember INNER JOIN Item AS MemberItem ON ( MemberItem.ID = ItemMember.Item ) LEFT OUTER JOIN ItemPrice ON ( ItemPrice.Item = MemberItem.ID ) AND ( ItemPrice.IsInactive = 'F' ) AND ( ItemPrice.PriceLevelName = 'List Price' ) WHERE ( ItemMember.ParentItem = ItemGroup.ID ) ) AS ListPrice FROM Item AS ItemGroup WHERE ItemGroup.ID = 681127 [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Items: Item Group Members Returns members of an Item Group, including their list prices. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Source: https://timdietrich.me/blog/netsuite-suiteql-item-groups/ SELECT ItemMember.Item, MemberItem.ItemID, MemberItem.Description, ItemMember.Quantity, ItemMember.MemberUnit, ItemPrice.Price AS UnitPrice, ( ItemPrice.Price \* ItemMember.Quantity ) AS ExtPrice FROM ItemMember INNER JOIN Item AS MemberItem ON ( MemberItem.ID = ItemMember.Item ) LEFT OUTER JOIN ItemPrice ON ( ItemPrice.Item = MemberItem.ID ) AND ( ItemPrice.IsInactive = 'F' ) AND ( ItemPrice.PriceLevelName = 'List Price' ) WHERE ItemMember.ParentItem = 99999 [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Items: Item Groups Returns a list of items that are setup as Item Groups. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-item-groups/ SELECT TOP 10 ID, ItemID FROM Item WHERE ItemType = 'Group' ORDER BY ID DESC [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Items: Pricing Returns price information, including price levels, for a given item. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-item-price-levels/ SELECT PriceLevel.Name, Pricing.PriceQty, Pricing.UnitPrice FROM PriceLevel LEFT OUTER JOIN Pricing ON ( Pricing.PriceLevel = PriceLevel.ID ) AND ( Pricing.Item = 1223 ) WHERE ( PriceLevel.IsInactive = 'F' ) ORDER BY PriceLevel.Name, Pricing.PriceQty [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Items: Item Information Returns detailed information about items that have been created or updated in the past 7 days. Item information includes the preferred vendor. Used in project involving development of a custom Product Information Manager (PIM). \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Source: https://timdietrich.me/blog/netsuite-suiteql-product-information-manager-sync/ SELECT Item.ID, Item.ItemID, Item.Parent, Item.ExternalID, Item.IsInactive, Item.IsOnline, Item.ItemType, Item.CreatedDate, Item.LastModifiedDate, Item.PurchaseDescription, Item.QuantityOnHand, Item.QuantityBackordered, Item.ReorderPoint, Item.Cost, Item.LeadTime, ItemVendor.Vendor AS PreferredVendorID, Vendor.Companyname AS PreferredVendorName, Vendor.Externalid AS VendorExternalID FROM Item LEFT OUTER JOIN ItemVendor ON ( ( ItemVendor.item = Item.id ) AND ( ItemVendor.preferredvendor = 'T' ) ) LEFT OUTER JOIN Vendor ON ( Vendor.id = ItemVendor.vendor ) WHERE ( Item.CreatedDate >= ( CURRENT\_DATE - 7 ) ) OR ( Item.LastModifiedDate >= ( CURRENT\_DATE - 7 ) ) [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Items: Item Types and Counts Returns a list of the types of items that are setup, and the count for each item type. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-item-types-counts/ SELECT ItemType, COUNT(\*) AS ItemCount FROM Item GROUP BY ItemType ORDER BY ItemType [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Items: Returned Items by Date Range Returns a list of items that were returned the most frequently in the previous year. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-returned-items/ SELECT TransactionLine.Item, BUILTIN.DF( TransactionLine.Item ) AS ItemName, Item.Manufacturer, SUM( TransactionLine.Quantity ) AS TotalQuantityReturned FROM Transaction INNER JOIN TransactionLine ON ( TransactionLine.Transaction = Transaction.ID ) INNER JOIN Item ON ( Item.ID = TransactionLine.Item ) WHERE ( Transaction.Type = 'RtnAuth' ) AND ( Transaction.Void = 'F' ) AND ( Transaction.Voided = 'F' ) AND ( Transaction.TranDate BETWEEN BUILTIN.RELATIVE\_RANGES( 'LY', 'START' ) AND BUILTIN.RELATIVE\_RANGES( 'LY', 'END' ) ) GROUP BY Item, BUILTIN.DF( TransactionLine.Item ), Manufacturer HAVING SUM( TransactionLine.Quantity ) > 0 ORDER BY TotalQuantityReturned DESC, ItemName ASC [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Items: Running Inventory Balance Returns the running inventory balance for a specified item. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Source: https://timdietrich.me/blog/netsuite-suiteql-item-transactions/ SELECT TransactionLine.Transaction AS TransactionID, Transaction.TranDate, Transaction.Type, Transaction.TranID, TransactionLine.Rate, TransactionLine.NetAmount, TransactionLine.Quantity, Entity.AltName, SUM( TransactionLine.Quantity ) OVER ( ORDER BY Transaction.TranDate, Transaction.TranID, TransactionLine.ID RANGE UNBOUNDED PRECEDING ) Qty\_Balance FROM TransactionLine INNER JOIN Transaction ON ( Transaction.ID = TransactionLine.Transaction ) LEFT OUTER JOIN Entity ON ( Entity.ID = Transaction.Entity ) WHERE ( TransactionLine.Item = 999999 ) AND ( TransactionLine.IsInventoryAffecting = 'T' ) AND ( Transaction.Voided = 'F' ) ORDER BY Transaction.TranDate, Transaction.TranID, TransactionLine.ID [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Items: Standalone Items Returns a list of items that exist entirely on their own, independent of a parent or child item. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-matrix-items/ SELECT Item.ID, Item.ItemID FROM Item WHERE ( Parent IS NULL ) AND ( IsInactive = 'F' ) AND ( NOT EXISTS ( SELECT \* FROM Item AS ChildItem WHERE ChildItem.Parent = Item.ID ) ) [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Items: Quantity Available Has Changed Returns a list of items whose quantity available has changed since a specified date. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-item-quantity-available-changes/ SELECT BUILTIN.DF( Item ) AS Item, BUILTIN.DF( Location ) AS Location, QuantityOnHand, QuantityCommitted, QuantityAvailable, QuantityBackOrdered, QuantityOnOrder, QuantityInTransit, TO\_CHAR ( LastQuantityAvailableChange, 'YYYY-MM-DD HH12:MI:SS PM' ) AS LastQtyAvailChangeTS FROM AggregateItemLocation WHERE ( LastQuantityAvailableChange > BUILTIN.RELATIVE\_RANGES( 'DAGO3', 'START' ) ) ORDER BY Item, Location [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Items: Vendors Returns a list of vendors that can provide a specified item. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-item-vendors/ SELECT BUILTIN.DF( ItemVendor.Item ) AS Item, Vendor.CompanyName AS Vendor, ItemVendor.PreferredVendor, ItemVendor.PurchasePrice, BUILTIN.DF( Vendor.Contact ) AS Contact, Vendor.Balance, Vendor.CreditLimit, BUILTIN.DF( Vendor.Terms ) AS Terms FROM ItemVendor INNER JOIN Vendor ON ( Vendor.ID = ItemVendor.Vendor ) WHERE ItemVendor.Item = 99999 ORDER BY ItemVendor.PreferredVendor DESC, Vendor [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Items: Summarized by Type Returns the number of active items group by item type. \-- Contributor: Tim Dietrich (timdietrich@me.com) SELECT ItemType, COUNT( ID ) AS ItemCount FROM Item WHERE IsInactive = 'F' GROUP BY ItemType ORDER BY ItemType [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Items: With Expiration Dates Returns items that have expiration dates. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-serialized-lot-number-expiring-items/ SELECT BUILTIN.DF( InventoryNumber.Item ) AS ItemID, InventoryNumber.InventoryNumber, InventoryNumber.QuantityOnHand, TO\_DATE( InventoryNumber.ExpirationDate ) - TO\_DATE( SYSDATE ) AS ExpirationDays FROM InventoryNumber WHERE InventoryNumber.ExpirationDate IS NOT NULL ORDER BY InventoryNumber.ExpirationDate [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Items: Without Preferred Bins Returns items that do not have a preferred bin. \-- Contributor: Tim Dietrich (timdietrich@me.com) SELECT Item.ID, Item.ItemID, BUILTIN.DF( Item.ItemType ) AS Type FROM Item WHERE Item.ID NOT IN ( SELECT Item FROM ItemBinQuantity WHERE PreferredBin = 'T' ) [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Journal Entries: With Details Returns journal entry transactions in a given date range, with line level details. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-journal-entries-gl-impact/ SELECT Transaction.ID, Transaction.TranID, Transaction.TranDate, BUILTIN.DF( Transaction.PostingPeriod ) AS PostingPeriod, Transaction.Memo, Transaction.Posting, BUILTIN.DF( Transaction.Status ) AS Status, BUILTIN.DF( Transaction.CreatedBy ) AS CreatedBy, BUILTIN.DF( TransactionAccountingLine.Account ) AS Account, TransactionAccountingLine.Debit, TransactionAccountingLine.Credit FROM Transaction INNER JOIN TransactionAccountingLine ON ( TransactionAccountingLine.Transaction = Transaction.ID ) WHERE ( Transaction.Type = 'Journal' ) AND ( Transaction.TranDate BETWEEN TO\_DATE( '2020-10-24', 'YYYY-MM-DD' ) AND TO\_DATE( '2020-10-31', 'YYYY-MM-DD' ) ) ORDER BY Transaction.ID DESC [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Journal Entries: In Date Range Returns journal entry transactions in a given date range. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-journal-entries-gl-impact/ SELECT Transaction.ID, Transaction.TranID, Transaction.TranDate, BUILTIN.DF( Transaction.PostingPeriod ) AS PostingPeriod, Transaction.Memo, Transaction.Posting, BUILTIN.DF( Transaction.Status ) AS Status, BUILTIN.DF( Transaction.CreatedBy ) AS CreatedBy FROM Transaction WHERE ( Transaction.Type = 'Journal' ) AND ( Transaction.TranDate BETWEEN TO\_DATE( '2020-10-24', 'YYYY-MM-DD' ) AND TO\_DATE( '2020-10-31', 'YYYY-MM-DD' ) ) ORDER BY Transaction.ID DESC [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Leads: For A Specific Sales Rep Returns leads assigned to a specified sales rep. \-- Source: https://timdietrich.me/blog/netsuite-suiteql-leads/ SELECT Customer.ID AS CustomerID, Customer.CompanyName, Customer.AltName, Customer.IsPerson, Customer.LastName, Customer.FirstName, Customer.Title, Customer.Email, Customer.Phone, Customer.DateCreated, Customer.LastModifiedDate FROM Customer WHERE ( Customer.SearchStage = 'Lead' ) AND ( Customer.SalesRep = 129 ) ORDER BY Customer.DateCreated DESC, Customer.CompanyName, Customer.AltName [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Leads: Summarized by Sales Rep Returns a summarized list of sales reps and the number of leads that they've been assigned. \-- Source: https://timdietrich.me/blog/netsuite-suiteql-leads/ SELECT Employee.LastName, Employee.FirstName, COUNT( Customer.ID ) AS LeadCount FROM Employee LEFT OUTER JOIN Customer ON ( Customer.SalesRep = Employee.ID ) AND ( Customer.SearchStage = 'Lead' ) WHERE ( Employee.IsInactive = 'F' ) AND ( Employee.IsSalesRep = 'T' ) GROUP BY Employee.LastName, Employee.FirstName ORDER BY Employee.LastName, Employee.FirstName [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Matrix Items: Child Items by Parent Returns all child items for a specified parent item. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-matrix-items/ SELECT Item.ID, Item.ItemID, Item.FullName, Item.Description FROM Item WHERE ( Item.Parent = 9998 ) AND ( Item.IsInactive = 'F' ) [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Matrix Items: Child Items Returns a child item and its parent item. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-matrix-items/ SELECT Item.ID, Item.ItemID, Item.FullName, Item.Description, ParentItem.ID AS ParentID, ParentItem.ItemID AS ParentItemID, ParentItem.FullName AS ParentItemFullName FROM Item INNER JOIN Item AS ParentItem ON ( ParentItem.ID = Item.Parent ) WHERE ( Item.ID = 9999 ) AND ( Item.IsInactive = 'F' ) [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Locations Returns a list of locations, including the main address. \-- Contributor: Tim Dietrich (timdietrich@me.com) SELECT Location.ID, Location.ExternalID, Location.Name, Location.FullName, -- Location.LocationType, -- Location.UseBins, LocationMainAddress.Addressee, LocationMainAddress.Addr1, LocationMainAddress.Addr2, LocationMainAddress.Addr3, LocationMainAddress.City, LocationMainAddress.State, LocationMainAddress.Zip, -- LocationMainAddress.Phone LocationMainAddress.Attention, Location.Latitude, Location.Longitude FROM Location INNER JOIN LocationMainAddress ON ( LocationMainAddress.nKey = Location.MainAddress ) WHERE Location.IsInactive = 'F' ORDER BY Location.ID [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Matrix Items: Parent Items Returns all parent items. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-matrix-items/ SELECT Item.ID, Item.ItemID, ( SELECT COUNT(\*) FROM Item AS ChildItem WHERE ChildItem.Parent = Item.ID ) AS ChildCount FROM Item WHERE ( Parent IS NULL ) AND ( IsInactive = 'F' ) AND ( EXISTS ( SELECT \* FROM Item AS ChildItem WHERE ChildItem.Parent = Item.ID ) ) [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Packages: Pacejet Package Information Returns information about item fulfillments and related Pacejet packages. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-item-fulfillments-pacejet-packages/ SELECT Transaction.ID, Transaction.TranID, Transaction.TranDate, Transaction.Entity AS CustomerID, BUILTIN.DF( Transaction.Entity ) AS CustomerName, ( SELECT DISTINCT SO.TranID FROM PreviousTransactionLineLink AS PTLL INNER JOIN Transaction AS SO ON ( SO.ID = PTLL.PreviousDoc ) WHERE ( PTLL.NextDoc = Transaction.ID ) AND ( SO.Type = 'SalesOrd' ) ) AS SalesOrder, CustRecord\_Pacejet\_Package\_ID AS Package\_ID, CustRecord\_Pacejet\_Package\_Tracking AS Tracking\_Number, CustRecord\_Pacejet\_Package\_Tracking\_Link AS Tracking\_Link, CustRecord\_Pacejet\_Package\_Contents AS Contents, CustRecord\_Pacejet\_Package\_Weight AS Weight FROM Transaction INNER JOIN MAP\_customrecord\_pacejet\_package\_info\_custrecord\_pacejet\_transaction\_link AS Map\_Links ON ( Map\_Links.MapTwo = Transaction.ID ) INNER JOIN CustomRecord\_Pacejet\_Package\_Info AS Package\_Info ON ( Package\_Info.ID = Map\_Links.MapOne ) WHERE ( Transaction .Type = 'ItemShip' ) AND ( Transaction.TranDate BETWEEN TO\_DATE( '2020-11-01 00:00:00', 'YYYY-MM-DD hh24:mi:ss' ) AND TO\_DATE( '2020-11-30 23:59:59', 'YYYY-MM-DD hh24:mi:ss' ) ) [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Pagination and Record Count Example Returns paginated results that includes the total number of rows found. \-- Source: https://timdietrich.me/blog/netsuite-suiteql-query-pagination/ WITH EmployeeData AS ( SELECT ID, FirstName, LastName, Email FROM Employee WHERE ( Email LIKE '%@netsuite.com' ) AND ( LastName IS NOT NULL ) AND ( FirstName IS NOT NULL ) ), EmployeeSummary AS ( SELECT COUNT(\*) AS RecordCount FROM EmployeeData ) SELECT \* FROM ( SELECT ROWNUM - 1 AS RN, \* FROM ( SELECT \* FROM ( SELECT RecordCount, NULL AS ID, NULL AS LastName, NULL AS FirstName, NULL AS Email FROM EmployeeSummary UNION ALL SELECT 0, EmployeeData.ID, EmployeeData.LastName, EmployeeData.FirstName, EmployeeData.Email FROM EmployeeData ) ORDER BY RecordCount DESC, UPPER( LastName ), UPPER( FirstName ) ) ) WHERE ( RN = 0 ) OR ( RN BETWEEN 1 AND 10 ) [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Phone Calls Returns calls related to a specific company, contact, etc. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-phone-calls/ SELECT PhoneCall.ID, PhoneCall.ExternalID, PhoneCall.CreatedDate, PhoneCall.StartDate, PhoneCall.CompletedDate, BUILTIN.DF( PhoneCall.Owner ) AS OwnerName, BUILTIN.DF( PhoneCall.Assigned ) As AssignedToName, PhoneCall.Company, Entity.AltName AS CompanyName, PhoneCall.Contact, BUILTIN.DF( PhoneCall.Contact ) AS ContactName, BUILTIN.DF( PhoneCall.Transaction ) AS TransactionName, BUILTIN.DF( PhoneCall.RelatedItem ) AS RelatedItemName, BUILTIN.DF( PhoneCall.SupportCase ) AS SupportCaseName, PhoneCall.Priority, PhoneCall.Status, PhoneCall.Phone, PhoneCall.Title, PhoneCall.Message FROM PhoneCall LEFT OUTER JOIN Entity ON ( Entity.ID = PhoneCall.Company ) WHERE ( PhoneCall.Company = 359 ) ORDER BY PhoneCall.CreatedDate DESC [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Purchase Orders: Summarized by Status Returns the various statuses of purchase orders, with the number of orders in each status, and the total amounts. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-order-status-summaries/ SELECT Status, BUILTIN.DF( Status ) AS StatusName, COUNT( \* ) AS TransactionCount, SUM( ( ForeignTotal \* -1) ) AS TotalAmount FROM Transaction WHERE ( Type = 'PurchOrd' ) GROUP BY Status, BUILTIN.DF( Status ) ORDER BY Status [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Purchase Orders: Item Receipts Returns items that have been received against POs in a given date range. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-purchase-order-item-receipts/ SELECT Transaction.TranDate AS DateReceived, BUILTIN.DF( Transaction.Entity ) AS Supplier, PO.TranID AS PONumber, POLine.LineSequenceNumber AS POLineNumber, Transaction.TranID AS ReceiptID, Item.ItemID, Item.Description AS ItemDescription, POLine.Quantity AS QuantityOrdered, TransactionLine.Quantity AS QuantityReceived, POLine.QuantityShipRecv AS TotalReceived, ( POLine.Quantity - POLine.QuantityShipRecv) AS QuantityOpen FROM Transaction INNER JOIN TransactionLine ON ( TransactionLine.Transaction = Transaction.ID ) AND ( TransactionLine.IsInventoryAffecting = 'T' ) INNER JOIN Transaction AS PO ON ( PO.ID = TransactionLine.CreatedFrom ) INNER JOIN Item ON ( Item.ID = TransactionLine.Item ) LEFT OUTER JOIN PreviousTransactionLineLink AS PTLL ON ( PTLL.NextType = 'ItemRcpt' ) AND ( PTLL.NextDoc = TransactionLine.Transaction ) AND ( PTLL.NextLine = TransactionLine.ID ) LEFT OUTER JOIN TransactionLine AS POLine ON ( POLine.Transaction = PO.ID ) AND ( POLine.linesequencenumber = PTLL.PreviousLine ) WHERE ( Transaction.Type = 'ItemRcpt' ) AND ( Transaction.TranDate BETWEEN TO\_DATE('2020-11-27', 'YYYY-MM-DD' ) AND TO\_DATE('2020-11-30', 'YYYY-MM-DD' ) ) AND ( Transaction.Voided = 'F' ) AND ( TransactionLine.Quantity <> 0 ) ORDER BY DateReceived, Supplier, PONumber, ItemID [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Purchase Orders: Related Transactions Returns a list of a purchase order's related transactions, including prepayments, item receipts, vendor bills, and more. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-purchase-orders-and-related-transactions/ SELECT PO.TranID AS PONumber, PO.TranDate AS DateOrdered, BUILTIN.DF( PO.Entity ) AS Supplier, BUILTIN.DF( PO.Status ) AS POStatus, ( PO.ForeignTotal \* -1 ) AS POAmount, NextTransaction.TranDate, BUILTIN.DF( NTL.NextDoc ) AS RelatedTransaction, BUILTIN.DF( NextTransaction.Status ) AS TransactionStatus, BUILTIN.DF( NextTransaction.ApprovalStatus ) AS ApprovalStatus, ( NextTransaction.ForeignTotal \* -1 ) AS TotalAmount, NextTransaction.ForeignAmountPaid AS AmountPaid, NextTransaction.ForeignAmountUnpaid AS BalanceDue, NextTransaction.Memo FROM Transaction AS PO INNER JOIN NextTransactionLink AS NTL ON ( NTL.PreviousDoc = PO.ID ) INNER JOIN Transaction AS NextTransaction ON ( NextTransaction.ID = NTL.NextDoc ) WHERE PO.ID = 33108 ORDER BY NextTransaction.TranDate, NextTransaction.ID [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Returns Authorizations: In Date Range Returns a list of Return Authorizations (RAs / RMAs) that have been issued in a specified date range. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-return-authorizations/ SELECT Transaction.TranDate, Transaction.TranID , BUILTIN.DF( Transaction.Entity ) AS Customer, BUILTIN.DF( Transaction.Status ) AS Status FROM Transaction WHERE ( Transaction.Type = 'RtnAuth' ) AND ( Transaction.TranDate BETWEEN TO\_DATE('2020-01-01', 'YYYY-MM-DD' ) AND TO\_DATE('2020-11-30', 'YYYY-MM-DD' ) ) AND ( Transaction.Voided = 'F' ) [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Returns Authorizations: Summarized by Customer Returns a list of Return Authorizations (RAs / RMAs) that have been issued in a specified date range, summarized by customer. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-return-authorizations/ SELECT BUILTIN.DF( Transaction.Entity ) AS Customer, COUNT(\*) AS RMACount, ( SUM( Transaction.ForeignTotal ) \* -1 ) AS RMATotal FROM Transaction WHERE ( Transaction.Type = 'RtnAuth' ) AND ( Transaction.TranDate BETWEEN TO\_DATE('2020-01-01', 'YYYY-MM-DD' ) AND TO\_DATE('2020-11-30', 'YYYY-MM-DD' ) ) AND ( Transaction.Voided = 'F' ) GROUP BY BUILTIN.DF( Transaction.Entity ) ORDER BY Customer [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Returns Authorizations: Summarized by Item Returns a list of Return Authorizations (RAs / RMAs) that have been issued in a specified date range, summarized by item. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-return-authorizations/ SELECT Item.ItemID, Item.Description, Count(\*) AS RMACount FROM Transaction INNER JOIN TransactionLine ON ( TransactionLine.Transaction = Transaction.ID ) INNER JOIN Item ON ( Item.ID = TransactionLine.Item ) WHERE ( Transaction.Type = 'RtnAuth' ) AND ( Transaction.TranDate BETWEEN TO\_DATE('2020-01-01', 'YYYY-MM-DD' ) AND TO\_DATE('2020-11-30', 'YYYY-MM-DD' ) ) AND ( Transaction.Voided = 'F' ) GROUP BY Item.ItemID, Item.Description ORDER BY RMACount DESC, Item.ItemID [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Roles: Active Roles Not Used Recently Returns the active roles that haven't been used in the past 90 days. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-roles-not-used/ SELECT Role.ID, Role.Name, Role.IsInactive FROM Role WHERE ( Role.IsInactive = 'F' ) AND ( ID NOT IN ( SELECT DISTINCT LoginAudit.Role FROM LoginAudit WHERE ( LoginAudit.Date >= ( SYSDATE - 90 ) ) AND ( LoginAudit.Role IS NOT NULL ) ) ) ORDER BY Role.Name [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Roles: Active Roles And The Date Last Used Returns all active roles, and includes the date that each role was most recently used. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-roles-not-used/ SELECT Role.ID, Role.Name, Role.IsInactive, ( SELECT MAX( LoginAudit.Date ) FROM LoginAudit WHERE LoginAudit.Role = Role. ID ) AS LastUsed FROM Role WHERE ( Role.IsInactive = 'F' ) ORDER BY Role.Name [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### ROLLUP: Oracle Command Example Used to append a summary row to the result set. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Reference: https://docs.oracle.com/cd/B13789\_01/olap.101/b10339/x\_reserved006.htm SELECT BUILTIN.DF( Status ) AS StatusName, COUNT( \* ) AS TransactionCount, SUM( ForeignTotal ) AS TotalAmount FROM Transaction WHERE ( Type = 'SalesOrd' ) GROUP BY ROLLUP( BUILTIN.DF( Status ) ) [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Sales Order: With Sales Tax Returns header-level values for a specified Sales Order, including the Tax Total value sourced from the related TransactionLine records. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-sales-tax/ SELECT Transaction.TranID, Transaction.TranDate, Transaction.Entity AS CustomerID, BUILTIN.DF( Transaction.Entity ) AS CustomerName, BUILTIN.DF( Transaction.Status ) AS Status, Transaction.ForeignTotal AS Total, ( SELECT SUM( TransactionLine.ForeignAmount \* -1 ) AS SalesTax FROM TransactionLine WHERE ( TransactionLine.Transaction = Transaction.ID ) AND ( TransactionLine.TaxLine = 'T' ) ) AS SalesTax FROM Transaction WHERE ( Transaction.Type = 'SalesOrd' ) AND ( Transaction.ID = 11708 ) [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Sales Orders: Summarized by Status Returns the various statuses of sales orders, with the number of orders in each status, and the total amounts. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-order-status-summaries/ SELECT Status, BUILTIN.DF( Status ) AS StatusName, COUNT( \* ) AS TransactionCount, SUM( ForeignTotal ) AS TotalAmount FROM Transaction WHERE ( Type = 'SalesOrd' ) GROUP BY Status, BUILTIN.DF( Status ) ORDER BY Status [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Sales Terms Returns a list of sales terms (also referred to as "Terms of Payment") that are setup in the instance. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Reference: https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/section\_N1234186.html SELECT ID, Name FROM Term WHERE isInactive = 'F' ORDER BY Name [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Sales Orders: Top Orders for the Current Day Returns the top sales orders of the day, based on the estimated gross profit. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-sales-related-queries/ SELECT \* FROM ( SELECT Transaction.TranID, Transaction.TranDate, Transaction.ShipDate AS ScheduledShipDate, Transaction.ActualShipDate, Transaction.ForeignTotal, Transaction.TotalCostEstimate, Transaction.EstGrossProfit, ROUND( ( Transaction.EstGrossProfitPercent \* 100 ), 2 ) AS GPPct, Employee.LastName, Employee.FirstName, BUILTIN.DF( Transaction.Entity ) AS Customer FROM Transaction INNER JOIN Employee ON ( Employee.ID = Transaction.Employee ) WHERE ( Transaction.Type = 'SalesOrd' ) AND ( Transaction.TranDate BETWEEN TO\_DATE( '2020-11-01', 'YYYY-MM-DD' ) AND TO\_DATE( '2020-11-30', 'YYYY-MM-DD' ) ) AND ( Transaction.Voided = 'F' ) ORDER BY Transaction.EstGrossProfit DESC ) WHERE ROWNUM <= 5 [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Sales Orders: Shipped Returns sales orders that shipped in a specified date range. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-sales-related-queries/ SELECT Transaction.TranID, Transaction.TranDate, Transaction.ShipDate AS ScheduledShipDate, Transaction.ActualShipDate, Transaction.ForeignTotal, Transaction.TotalCostEstimate, Transaction.EstGrossProfit, ROUND( ( Transaction.EstGrossProfitPercent \* 100 ), 2 ) AS GPPct, Employee.LastName, Employee.FirstName, BUILTIN.DF( Transaction.Entity ) AS Customer FROM Transaction INNER JOIN Employee ON ( Employee.ID = Transaction.Employee ) WHERE ( Transaction.Type = 'SalesOrd' ) AND ( Transaction.ActualShipDate BETWEEN TO\_DATE( '2020-10-24', 'YYYY-MM-DD' ) AND TO\_DATE( '2020-10-31', 'YYYY-MM-DD' ) ) AND ( Transaction.Voided = 'F' ) [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Shipments: Inbound Returns all inbound shipments, including expected and actual ship dates, expected delivery dates, and more. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-inbound-shipments/ SELECT InboundShipment.ID AS InboundShipmentID, BUILTIN.DF( POTransaction.Entity ) AS Vendor, POTransaction.TranID AS PONumber, InboundShipment.ShipmentStatus, InboundShipment.ExpectedShippingDate, InboundShipment.ActualShippingDate, InboundShipment.ExpectedDeliveryDate, BUILTIN.DF( InboundShipmentItem.ReceivingLocation ) AS ReceivingLocation, InboundShipment.ShipmentMemo, BUILTIN.DF( POLine.Item ) AS ItemID, InboundShipmentItem.ShipmentItemDescription, InboundShipmentItem.QuantityExpected, BUILTIN.DF( InboundShipmentItem.Unit ) AS Units, InboundShipmentItem.ExpectedRate, InboundShipment.ExternalDocumentNumber, InboundShipment.BillOfLading FROM InboundShipment INNER JOIN InboundShipmentItem ON ( InboundShipmentItem.inboundshipment = inboundshipment.ID ) INNER JOIN Transaction AS POTransaction ON ( POTransaction.ID = InboundShipmentItem.purchaseordertransaction ) INNER JOIN TransactionLine AS POLine ON ( POLine.uniquekey = InboundShipmentItem.shipmentitemtransaction ) WHERE ( InboundShipment.ShipmentStatus <> 'received' ) ORDER BY InboundShipment.ExpectedDeliveryDate, InboundShipment.ID [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Sales Pipeline Returns a list of sales orders that are scheduled to ship in a specified date range. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Reference: https://timdietrich.me/blog/netsuite-suiteql-sales-related-queries/ SELECT Transaction.TranID, Transaction.TranDate, Transaction.ShipDate AS ScheduledShipDate, Transaction.ActualShipDate, Transaction.ForeignTotal, Transaction.TotalCostEstimate, Transaction.EstGrossProfit, ROUND( ( Transaction.EstGrossProfitPercent \* 100 ), 2 ) AS GPPct, Employee.LastName, Employee.FirstName, BUILTIN.DF( Transaction.Entity ) AS Customer FROM Transaction INNER JOIN Employee ON ( Employee.ID = Transaction.Employee ) WHERE ( Transaction.Type = 'SalesOrd' ) AND ( Transaction.ShipDate BETWEEN TO\_DATE( '2020-10-24', 'YYYY-MM-DD' ) AND TO\_DATE( '2020-10-31', 'YYYY-MM-DD' ) ) AND ( Transaction.Voided = 'F' ) [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Shipments: Details Returns detailed shipment information based on date criteria. Includes the customer, sales order, customer po, the warehouse, that it shipped from, carrier / method, total weight, number of items shipped, tracking number(s), and the items / quantities that shipped. \-- Contributor: Tim Dietrich (timdietrich@me.com) SELECT Transaction.TranDate AS Date, BUILTIN.DF( Transaction.Entity ) AS CustomerName, Transaction.Entity AS CustomerID, SalesOrder.TranID AS SalesOrder, SalesOrder.OtherRefNum AS CustomerPO, BUILTIN.DF( Mainline.Location ) AS Warehouse, BUILTIN.DF( Transaction.ShippingAddress ) AS Address, Transaction.ShipCarrier AS Carrier, TransactionShipment.ShippingMethod AS Method, TransactionShipment.Weight, ( SELECT ( SUM( Quantity ) \* -1 ) FROM TransactionLine WHERE ( TransactionLine.Transaction = Transaction.ID ) AND ( TransactionLine.IsInventoryAffecting = 'T' ) ) AS ItemCount, ( SELECT LISTAGG( TrackingNumber, ', ' ) FROM ( SELECT DISTINCT TrackingNumber.TrackingNumber FROM TrackingNumberMap INNER JOIN TrackingNumber ON ( TrackingNumber.ID = TrackingNumberMap.TrackingNumber ) WHERE TrackingNumberMap.Transaction = Transaction.ID ORDER BY TrackingNumber.TrackingNumber ) ) AS TrackingNumbers, ( SELECT LISTAGG( TO\_CHAR( Quantity \* -1 ) || ' x ' || BUILTIN.DF( TransactionLine.Item ), ', ' ) FROM TransactionLine WHERE TransactionLine.Transaction = Transaction.ID AND ( TransactionLine.IsInventoryAffecting = 'T' ) ) AS Items FROM Transaction INNER JOIN TransactionLine AS Mainline ON ( Mainline.Transaction = Transaction.ID ) AND ( Mainline.MainLine = 'T' ) INNER JOIN Transaction AS SalesOrder ON ( SalesOrder.ID = Mainline.CreatedFrom ) INNER JOIN TransactionShipment ON ( TransactionShipment.Doc = Transaction.ID ) WHERE ( Transaction.Type = 'ItemShip' ) AND ( Transaction.TranDate >= TO\_DATE( '2024-10-01', 'YYYY-MM-DD' ) ) AND ( Transaction.Void = 'F' ) AND ( BUILTIN.CF( Transaction.Status ) = 'ItemShip:C' ) ORDER BY Date, CustomerName [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Support Cases: Closed: Summarized by Assignee Returns closed support cases summarized by the employee that they are assigned to. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-support-cases/ SELECT BUILTIN.DF( Assigned ) AS AssignedTo, COUNT(\*) AS ClosedCaseCount, ROUND( AVG( TimeToClose ), 0 ) AS AvgHoursToClose FROM SupportCase WHERE -- Closed or Solved. ( Status IN ( 5, 7 ) ) AND ( ( StartDate + ( ( 1/24 ) \* TimeToClose ) ) BETWEEN TO\_DATE( '2020-01-01', 'YYYY-MM-DD' ) AND TO\_DATE( '2020-12-31', 'YYYY-MM-DD' ) ) GROUP BY BUILTIN.DF( Assigned ) ORDER BY AssignedTo [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Support Cases: Open: Summarized by Assignee Returns open support cases summarized by the employee that they are assigned to. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-support-cases/ SELECT BUILTIN.DF( Assigned ) AS AssignedTo, COUNT(\*) AS OpenCaseCount, TRUNC( AVG( SYSDATE - StartDate ) ) AS AvgDaysOpen, TRUNC( AVG( ( SYSDATE - StartDate) \* 24 ) ) AS AvgHoursOpen FROM SupportCase WHERE -- Not Closed or Solved. ( Status NOT IN ( 5, 7 ) ) GROUP BY BUILTIN.DF( Assigned ) ORDER BY AssignedTo [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### States Returns a list of states that NetSuite supports. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-states-countries/ SELECT State.ID, State.ShortName, State.FullName, State.Country FROM State ORDER BY State.ShortName [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Shipments: Outbound Returns all shipments (item fulfillments) made within a specified date range. Includes tracking numbers, package information (ship from and to addresses, weight, shipping / handles charges), and more. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-shipments/ SELECT Transaction.ID, Transaction.TranID, Transaction.TranDate, BUILTIN.DF( Transaction.Entity ) AS Customer, SalesOrder.TranID AS SONumber, SalesOrder.OtherRefNum AS PONumber, BUILTIN.DF( TransactionShipment.SourceAddress ) AS ShipFrom, BUILTIN.DF( TransactionShipment.DestinationAddress ) AS ShipTo, BUILTIN.DF( TransactionShipment.ShippingMethod ) AS ShipMethod, TransactionShipment.Weight, TransactionShipment.ShippingRate, TransactionShipment.HandlingRate, BUILTIN.DF( Transaction.TrackingNumberList ) AS TrackingNumbers FROM Transaction INNER JOIN TransactionShipment ON ( TransactionShipment.Doc = Transaction.ID ) INNER JOIN TransactionLine ON ( TransactionLine.Transaction = Transaction.ID ) AND ( TransactionLine.MainLine = 'T' ) INNER JOIN Transaction AS SalesOrder ON ( SalesOrder.ID = TransactionLine.CreatedFrom ) WHERE ( Transaction.Type = 'ItemShip' ) AND ( Transaction.TranDate >= BUILTIN.RELATIVE\_RANGES( 'DAGO30', 'START' ) ) ORDER BY Transaction.TranDate DESC, Transaction.TranID DESC [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Support Cases: Open: Detailed Returns details about open support cases. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-support-cases/ SELECT SupportCase.ID, SupportCase.StartDate, SupportCase.CaseNumber, BUILTIN.DF( SupportCase.Status ) AS Status, Customer.EntityTitle AS Customer, SupportCase.Title AS Subject, SupportCase.Issue, BUILTIN.DF( SupportCase.Category ) AS Category, BUILTIN.DF( SupportCase.Assigned ) AS AssignedTo, BUILTIN.DF( SupportCase.Origin ) AS Origin, BUILTIN.DF( SupportCase.Priority ) AS Priority, SupportCase.TimeElapsed, SupportCase.TimeOpen, SupportCase.TimeToAssign FROM SupportCase INNER JOIN Entity AS Customer ON ( Customer.ID = SupportCase.Company ) WHERE -- Not Closed or Solved. ( SupportCase.Status NOT IN ( 5, 7 ) ) ORDER BY SupportCase.TimeOpen DESC [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Support Cases: Open: Summarized by Status Returns open support cases summarized by status. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-support-cases/ SELECT BUILTIN.DF( Status ) AS Status, COUNT(\*) AS OpenCaseCount, TRUNC( AVG( SYSDATE - StartDate ) ) AS AvgDaysOpen, TRUNC( AVG( ( SYSDATE - StartDate) \* 24 ) ) AS AvgHoursOpen FROM SupportCase WHERE -- Not Closed or Solved. ( Status NOT IN ( 5, 7 ) ) GROUP BY BUILTIN.DF( Status ) ORDER BY Status [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Transactions: GL Impact Returns the GL impact for a specified transaction. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-journal-entries-gl-impact/ SELECT BUILTIN.DF( TransactionAccountingLine.Account ) AS Account, TransactionAccountingLine.Debit, TransactionAccountingLine.Credit, TransactionAccountingLine.Posting, TransactionLine.Memo FROM TransactionAccountingLine INNER JOIN TransactionLine ON ( TransactionLine.Transaction = TransactionAccountingLine. Transaction ) AND ( TransactionLine.ID = TransactionAccountingLine. TransactionLine ) WHERE ( TransactionAccountingLine.Transaction = 88417362 ) AND ( ( TransactionAccountingLine.Debit IS NOT NULL ) OR ( TransactionAccountingLine.Credit IS NOT NULL ) ) ORDER BY TransactionLine.ID [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Transaction Counts: By Type and Status Returns the total number of transactions in each type / status combination. \-- Contributor: Tim Dietrich (timdietrich@me.com) SELECT Transaction.Type AS TransactionType, BUILTIN.DF( Status ) AS Status, COUNT(\*) AS Count FROM Transaction GROUP BY Transaction.Type, BUILTIN.DF( Status ) ORDER BY TransactionType, Status [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Transaction Line Usage Returns the number of Transaction Lines generated in the last 6 months, grouped by transaction type. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-transaction-line-usage/ SELECT \* FROM ( SELECT TO\_CHAR( Transaction.CreatedDate, 'YYYY-MM' ) AS TranMonth, Transaction.Type AS TranType, COUNT(\*) AS TranLineCount FROM Transaction INNER JOIN TransactionLine ON ( TransactionLine.Transaction = Transaction.ID ) WHERE ( Transaction.CreatedDate BETWEEN BUILTIN.RELATIVE\_RANGES( 'LRH', 'START' ) AND BUILTIN.RELATIVE\_RANGES( 'LRH', 'END' ) ) GROUP BY TO\_CHAR( Transaction.CreatedDate, 'YYYY-MM' ), Transaction.Type ) ORDER BY TranMonth, TranType [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Taxes - AvaTax Transaction Logs Returns information about the AvaTax API calls that were made for a specified transaction. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-avalara-avatax/ SELECT AVATRANLOG.ID, TO\_CHAR ( AVATRANLOG.CREATED, 'DS TS' ) AS CREATED, CUSTRECORD\_AVA\_TITLE, CUSTRECORD\_AVA\_NOTE FROM CUSTOMRECORD\_AVATRANSACTIONLOGS AS AVATRANLOG WHERE ( AVATRANLOG.CUSTRECORD\_AVA\_TRANSACTION = 60809426 ) [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Transactions: Statuses Returns a list status codes for all transaction types. \-- Contributor: Tim Dietrich (timdietrich@me.com) SELECT DISTINCT Transaction.Type AS TransactionType, Status, BUILTIN.DF( Status ) AS DFStatus, BUILTIN.CF( Status ) AS CFStatus FROM Transaction ORDER BY TransactionType, Status [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Transactions: GL Impact: Revisited Returns the GL impact for a specified transaction. \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-gl-impact/ SELECT Transaction.TranID, BUILTIN.DF( TransactionAccountingLine.AccountingBook ) AS AccountingBookName, BUILTIN.DF( TransactionAccountingLine.Account ) AS Account, TransactionAccountingLine.Debit, TransactionAccountingLine.Credit, TransactionAccountingLine.Posting, BUILTIN.DF( Transaction.Entity ) AS EntityName, TransactionLine.Memo, BUILTIN.DF( TransactionLine.Subsidiary ) AS Subsidiary, BUILTIN.DF( TransactionLine.Department ) AS Department FROM Transaction INNER JOIN TransactionAccountingLine ON ( TransactionAccountingLine.Transaction = Transaction.ID ) LEFT OUTER JOIN TransactionLine ON ( TransactionLine.Transaction = TransactionAccountingLine.Transaction ) AND ( TransactionLine.LineSequenceNumber = TransactionAccountingLine.TransactionLine ) WHERE ( Transaction.ID = 26254 ) AND ( TransactionAccountingLine.Account IS NOT NULL ) ORDER BY TransactionAccountingLine.TransactionLine [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. --- # SuiteQL Query Library | Tim Dietrich SuiteQL Query Library --------------------- #### Vendors: Vendor Information Returns detailed information about vendors that have been created or updated in the past 7 days. Used in project involving development of a custom Product Information Manager (PIM). \-- Contributor: Tim Dietrich (timdietrich@me.com) -- Source: https://timdietrich.me/blog/netsuite-suiteql-product-information-manager-sync/ SELECT Vendor.ID, Vendor.IsInactive, Vendor.CompanyName, Vendor.AccountNumber, Vendor.Balance, Vendor.Email, Vendor.Phone, BUILTIN.DF( Vendor.Terms ) AS Terms, Vendor.DateCreated, Vendor.LastModifiedDate FROM Vendor WHERE ( Vendor.DateCreated >= ( CURRENT\_DATE - 7 ) ) OR ( Vendor.LastModifiedDate >= ( CURRENT\_DATE - 7 ) ) [Click here](/suiteql-query-library/) to return to the list of available queries. About Me Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on [NetSuite](https://netsuite.com) , including mobile apps, Web portals, Web APIs, and more. I'm the developer of several popular NetSuite open source solutions, including the [SuiteQL Query Tool](../netsuite-suitescripts/suiteql-query-tool/ "SuiteQL Query Tool") , [SuiteAPI](https://suiteapi.com) , and [more](../netsuite-suitescripts/ "NetSuite SuiteScripts") . Copyright © 2025 Tim Dietrich. ---