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

Parcel Data

Parcel data is more complex because:

• One order/stop can have multiple parcels.

• Parcel data can be large, so filter carefully (by dates, customers, or IDs) to avoid slow queries.

Parcel information often involves scanning events (e.g., when a parcel is received, loaded, or delivered). We provide multiple views and functions to simplify this.

Order Parcel Data

Key Point: Order-based parcel views/functions focus on parcels associated with a given order, without repeating the full order data for every parcel.

qryOrderParcel_NoOrderData

• What It Does: Returns all parcel and scan history data for an order without re-listing the order details. To get complete info, join it with qryOrder.

• Use Case: For detailed parcel-level reporting (tracking scans, statuses) tied to orders.

EXAMPLE

getOrderParcelScans (Function)

• What It Does: Similar to qryOrderParcel_NoOrderData but implemented as a function.

• Use Case: If you prefer using a function rather than a view or need a dynamic approach.

EXAMPLE

Route Stop Parcel Data

Route stop parcel data is more nuanced because:

• Parcels may have multiple events, each tied to different stops (e.g., receive at one stop, load at another, deliver at a third).

• Ownership of parcels (which stop “owns” the parcel) affects which stops and data you’ll see.

We provide functions and views that simplify these relationships so you can understand where and when parcels were scanned.

getRouteStopParcelScans (Function)

• What It Does: Returns parcel scan events for a route stop, letting you define which stop types correspond to receive, load, and delivery.

• Use Case: Helpful if your configuration differs from the defaults and you need to map different stop types to different parcel events.

• Default Stop Types: Receive = 5, Load = 0, Delivery = 1 (but you can change them).

EXAMPLE(using defaults):

EXAMPLE (custom stop types):
Suppose your receive stop type is 2 and your load stop type is 5, with delivery defaulting to 1:

getRouteStopParcelAssignmentData (Function)

• What It Does: Returns the top 5 parcel-related assignments for a route stop, without requiring you to define stop types. This provides a more generic data set that you can interpret as needed.

• Use Case: Best if your setup doesn’t follow standard chaining or if you need a quick overview of multiple parcel events without customization.

• Performance Tip: Always filter down to a small set of stops.

EXAMPLE

qryOrderRouteStops_Parcels (View)

• What It Does: View-based alternative to getRouteStopParcelAssignmentData.

• Limit: Only returns data for stops created in the last 60 days to improve performance.

• Use Case: Great for smaller queries when you don’t want to set up functions.

EXAMPLE

Status Code Data

Status codes represent various events or states in the lifecycle of an order or route stop. Since status codes can be numerous, these tools help you focus on the latest status code to avoid duplicates.

getOrderLastStatusCode (Function)

• What It Does: Returns the last (most recent) status code for an order.

• Use Case: Quickly find the current state of an order.

EXAMPLE

qryOrder_LastStatusCode (View)

• What It Does: Combines order data with the last status code logic.

• Use Case: A view-based approach to easily query the latest status without using a function.

EXAMPLE

getRouteStopLastStatusCode (Function)

• What It Does: Returns the last status code for a route stop.

• Use Case: Useful for finding the current state of a particular stop.

EXAMPLE

qryOrderRouteStops_LastStatusCode (View)

• What It Does: Combines route stop data with the last status code logic.

• Use Case: A view-based solution similar to qryOrder_LastStatusCode but for route stops.

EXAMPLE

Need More Information?

We are looking to continually improve this guide and your reporting capabilities. If there’s data you need that isn’t available in one of these views or functions, please contact us. We’ll evaluate whether to create new views or enhance existing ones. Our goal is to ensure you can build all your queries without intimate knowledge of our many database tables and their complex relationships.