Pre-Built Database Views and Functions

This guide is designed to help you easily pull data from CXT Software’s platform using our pre-built database views and functions. These views and functions simplify the process of writing SQL queries so you don’t have to understand complex database details or join multiple tables yourself. All you need to do is apply the right filters for your needs.

• Order Data

• Route Stop Data

• Contract Data

• Contract Stop Data

• Parcel Data

• Order Parcel Data

• Route Stop Parcel Data

• Status Code Data

Key Concepts:

• Views are like predefined, “ready-to-use” queries. Instead of remembering how tables link together, you just select from a view.

• Functions are pre-built, reusable snippets of logic that return data. Think of them as special commands you can use in your queries.

Important Tips:

1. Always Use Filters: Make sure to filter by date ranges, customer IDs, or other criteria to avoid returning unnecessary data and to keep queries running quickly.

2. No Direct Table References: Our goal is for you to never need to query raw tables directly. If you cannot find what you need in a view or function, let us know so we can create or update one.

3. About WITH (NOLOCK):

   • Many examples use the WITH (NOLOCK) hint. This can improve query performance by reducing locking overhead, making your queries can run faster.

   • However, WITH (NOLOCK) may return data that is still being updated (known as “dirty reads”), so the data might not always be fully up-to-date.

   • You are not required to use WITH (NOLOCK). If data consistency is critical and performance is not, consider omitting it.

Order Data

When pulling order data, you’ll typically use these views to get all relevant information, such as order details, times, customers, and financial data (if needed).

qryOrder

• What It Does: Returns comprehensive order details, including financial information.

• Use Case: Ideal if you want a full picture of the order—times, costs, customer info, etc.

EXAMPLE

qryOrder_NoFinancials

• What It Does: Returns the same order details as qryOrder but without financial data.

• Use Case: Perfect if you’re sharing data with another party but don’t want them to see pricing or financials.

EXAMPLE

qryOrderByDriver

• What It Does: Shows each driver associated with an order. You’ll get multiple rows per order if more than one driver is involved.

• Use Case: Useful when analyzing driver assignments or performance metrics on a per-order basis.

• Note: Be careful with aggregate functions (like SUM) because data may repeat if multiple drivers are assigned.

EXAMPLE

Route Stop Data

Route stops can represent pick-ups, deliveries, loads, receives, and other stops along a route. 

qryOrderRouteStops

• What It Does: Returns details for each route stop, similar to how the “basic find” in the UI works.

• Use Case: Ideal for reports on all stops performed in a certain time frame or for a certain route.

EXAMPLE

qryOrderRouteStops_NoFinancials

• What It Does: Same as qryOrderRouteStops but without any financial details.

• Use Case: Good if sharing stop-level data with a party who doesn’t need cost information.

EXAMPLE

Contract Data

qryContract_NoFinancials

• What It Does: Returns contract information without financial details.

• Use Case: Ideal for a contract overview when pricing isn’t needed.

EXAMPLE

Contract Stop Data

qryContractStop

• What It Does: Returns details about contract stops.

• Use Case: Helpful for analyzing or reporting on regular, recurring stops.

EXAMPLE

qryContractStop_NoFinancials

• What It Does: Same as qryContractStop but excludes any financial details.

• Use Case: Useful if you want recurring stop details without cost info.

EXAMPLE