> ## Documentation Index
> Fetch the complete documentation index at: https://docs.jaantonio.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Query relational data

> Turn a business question into safe joins, filters, totals, and comparisons over CargoWise data.

Analytics gives you connected business relations, not one table per report. Use this process for a
new question:

1. Define what one result row should mean.
2. Start from the view with that grain.
3. Map each condition to a related view and role.
4. Inspect the values your CargoWise feed actually uses.
5. Filter repeating detail with `EXISTS`.
6. Check units, missing values, and row counts before sharing the result.

## 1. Choose the answer’s grain

The **grain** is what one row represents.

| You want to count, sum, or compare… | Start from…                      | One row means…                  |
| ----------------------------------- | -------------------------------- | ------------------------------- |
| Shipments or shipment weight        | `reporting.shipments`            | One current Forwarding Shipment |
| One-Off Quotes                      | `reporting.one_off_quotes`       | One current OneOffQuote         |
| Charges                             | `reporting.entity_charges`       | One charge line on one entity   |
| Packing lines                       | `reporting.entity_packing_lines` | One packing line                |
| Milestones                          | `reporting.entity_milestones`    | One milestone                   |

If the answer is shipment weight, start from `shipments`. Dates, staff, parties, locations, and
charges decide which shipments qualify. They do not change the grain of the answer.

## 2. Connect related rows with keys

`entity_id` is the main relationship key:

```text theme={null}
shipments.entity_id
  ├── shipment_dates.entity_id
  ├── entity_locations.entity_id
  ├── entity_staff_assignments.entity_id
  ├── entity_parties.entity_id
  ├── shipment_charges.entity_id
  ├── shipment_transport_legs.entity_id
  └── entity_packing_lines.entity_id
```

Use job numbers, names, descriptions, and business codes for filters and display. Do not use them as
join keys unless [Reporting views](/analytics/reporting-views) documents that relationship.

## 3. Start with the primary view

Begin with a small query and confirm its grain:

```sql theme={null}
SELECT
  entity_id,
  job_number,
  direction,
  transport_mode,
  origin_code,
  destination_code,
  total_weight,
  total_weight_unit,
  total_weight_kg
FROM reporting.shipments
WHERE transport_mode = 'SEA'
ORDER BY job_number;
```

`total_weight` and `total_weight_unit` preserve the source. `total_weight_kg` is populated only when
Analytics recognizes the source unit and can convert it safely.

## 4. Inspect the values before filtering

CargoWise codes and roles can differ by organization. See which values exist before writing the
final conditions:

```sql theme={null}
WITH observed_values AS (
  SELECT 'direction' AS field, direction AS value
  FROM reporting.shipments

  UNION ALL

  SELECT 'date_type_code', dates.date_type_code
  FROM reporting.shipment_dates AS dates

  UNION ALL

  SELECT 'location_role', locations.location_role
  FROM reporting.entity_locations AS locations
  JOIN reporting.shipments AS shipments
    ON shipments.entity_id = locations.entity_id

  UNION ALL

  SELECT 'staff_role', staff.staff_role
  FROM reporting.entity_staff_assignments AS staff
  JOIN reporting.shipments AS shipments
    ON shipments.entity_id = staff.entity_id

  UNION ALL

  SELECT 'charge_code', charges.charge_code
  FROM reporting.shipment_charges AS charges
)
SELECT
  field,
  value,
  count(*) AS rows_using_value
FROM observed_values
WHERE value IS NOT NULL
GROUP BY field, value
ORDER BY field, rows_using_value DESC, value;
```

Inspect names and descriptions too when you do not know a code. Once you find the intended value,
prefer the stable source code for recurring queries.

## 5. Add one repeating relation

A shipment can have several dates. Choose the CargoWise meaning through `date_type_code`:

```sql theme={null}
SELECT
  shipments.job_number,
  dates.date_type_code,
  dates.date_type_description,
  dates.is_estimate,
  dates.date_value_local,
  dates.date_value_utc
FROM reporting.shipments AS shipments
JOIN reporting.shipment_dates AS dates
  ON dates.entity_id = shipments.entity_id
WHERE dates.date_type_code = 'ETD'
ORDER BY dates.date_value_local DESC;
```

`date_value_local` preserves the source text. `date_value_utc` is available only when CargoWise sent
an explicit offset. Confirm the local format before using it for a range filter.

## 6. Compose a multi-condition question

Suppose you want to compare sea lanes for shipments that:

* have an estimated departure during April
* belong to a particular consignor
* are assigned to a particular salesperson
* include a posted freight sell charge

First replace the example codes with values observed in your data. Then keep the result at shipment
grain and use one `EXISTS` block for each repeating relation:

```sql theme={null}
WITH eligible_shipments AS (
  SELECT
    shipments.entity_id,
    shipments.origin_code,
    shipments.destination_code,
    shipments.total_weight_kg
  FROM reporting.shipments AS shipments
  WHERE shipments.transport_mode = 'SEA'
    AND EXISTS (
      SELECT 1
      FROM reporting.shipment_dates AS dates
      WHERE dates.entity_id = shipments.entity_id
        AND dates.date_type_code = 'ETD'
        AND dates.is_estimate IS TRUE
        AND dates.date_value_local >= '2026-04-01'
        AND dates.date_value_local < '2026-05-01'
    )
    AND EXISTS (
      SELECT 1
      FROM reporting.entity_parties AS parties
      WHERE parties.entity_id = shipments.entity_id
        AND parties.party_role = 'ConsignorDocumentaryAddress'
        AND parties.organization_code = 'ACME'
    )
    AND EXISTS (
      SELECT 1
      FROM reporting.entity_staff_assignments AS staff
      WHERE staff.entity_id = shipments.entity_id
        AND staff.staff_role = 'sales'
        AND staff.staff_code = 'ELENA'
    )
    AND EXISTS (
      SELECT 1
      FROM reporting.shipment_charges AS charges
      WHERE charges.entity_id = shipments.entity_id
        AND charges.charge_code = 'FRT'
        AND charges.sell_is_posted IS TRUE
        AND charges.sell_local_amount > 0
    )
)
SELECT
  origin_code,
  destination_code,
  count(*) AS shipment_count,
  sum(total_weight_kg) AS transported_weight_kg,
  count(*) FILTER (WHERE total_weight_kg IS NULL) AS shipments_without_normalized_weight
FROM eligible_shipments
GROUP BY origin_code, destination_code
ORDER BY transported_weight_kg DESC NULLS LAST;
```

The query relates five business concepts without asking Jaantonio for a report-specific view. Change
the starting grain, roles, codes, measures, and grouping to answer a different question.

<Warning>
  `ETD`, `ConsignorDocumentaryAddress`, `ACME`, `sales`, `ELENA`, and `FRT` are illustrative source
  values. Use the codes and meanings you confirmed in your own CargoWise data.
</Warning>

## Why `EXISTS` protects totals

Suppose one shipment has:

* 2 matching dates
* 3 matching charges
* 2 matching staff assignments

A direct join can produce `2 × 3 × 2 = 12` rows for that shipment. Summing shipment weight over
those rows counts the same weight 12 times.

Use:

* `EXISTS` when detail rows only decide whether a shipment or quote qualifies
* a pre-aggregated subquery when you need one result per `entity_id`
* the detail view itself when the answer’s grain is a charge, date, packing line, or milestone

## Query One-Off Quotes

`reporting.one_off_quotes` is one current row per OneOffQuote. It follows the same `entity_id`
pattern.

Use `reporting.quote_dates` and `reporting.quote_charges` for quote-only detail. Join the generic
staff, party, location, custom-field, and property views through `entity_id`.

```sql theme={null}
SELECT
  quotes.quote_number,
  quotes.transport_mode,
  quotes.origin_code,
  quotes.destination_code,
  quotes.total_weight_kg,
  quotes.is_approved,
  quotes.total_revenue,
  quotes.total_job_profit
FROM reporting.one_off_quotes AS quotes
WHERE quotes.is_approved IS TRUE
ORDER BY quotes.quote_number;
```

## Relate a quote to a shipment

Equal displayed numbers do not prove that a quote became a shipment. Use the explicit relationship:

```sql theme={null}
SELECT DISTINCT
  quotes.quote_number,
  shipments.job_number
FROM reporting.one_off_quotes AS quotes
JOIN reporting.entity_relationships AS relationships
  ON relationships.from_entity_id = quotes.entity_id
 AND relationships.relationship_type = 'quote_to_shipment'
JOIN reporting.shipments AS shipments
  ON shipments.entity_id = relationships.to_entity_id;
```

`entity_relationships` keeps observed relationship evidence across source versions, so use
`DISTINCT` when you need one business pair.

## Validate before sharing an answer

Check:

* one primary row still means one shipment or quote
* every role and code matches the intended business meaning
* normalized units are populated for the rows you aggregate
* currencies are not mixed when you aggregate charges
* nulls and unrecognized values are counted, not silently forgotten
* a few known CargoWise records produce the expected result

If a needed value is not a dedicated column, continue to
[Discover fields](/analytics/discover-fields).