How to write queries for shipment data analysis
Ticketclosed
Brakkevoort Logistics: documents · How to write queries for shipment data analysis
Customer building shipment analysis queries got inconsistent results. Bethan explained timestamp handling and the need for GROUP BY, resolving the issue.
What happened
What this says
Current to 11 Oct 26Customer building shipment analysis queries got inconsistent results. Bethan explained timestamp handling and the need for GROUP BY, resolving the issue.
Problem: Bas Vermeulen writing queries on shipment data was getting different record counts depending on query structure. Handling: Bethan identified two issues: the timestamp field includes a time component (catches at boundaries with date-only filters) and shipments can have multiple status records (requiring GROUP BY to avoid double-counting). She provided the correct query pattern and Bas confirmed it worked.
Details
Updated 9 Oct 26Trail
created · Bas Vermeulen 7 Aug 2026 17:20Hi, I'm trying to set up a way for our account managers to query shipments by customer reliably, but we're getting inconsistent results. I've written a couple of queries and they're returning different counts depending on how I structure them. Not sure if it's a syntax issue or the way I'm filtering. We need this working reliably, can you walk me through the right approach?- public_reply · Bethan Lister 7 Aug 2026 17:54Thanks for that. Just so I've got this right: you're writing queries to find shipments by customer and date, and you're getting different numbers depending on the query? Can you share the two versions and what counts they're returning? That'll help me see what's happening.
- public_reply · Bethan Lister 10 Aug 2026 12:57I've picked this up now. The issue is how your timestamp field works. Your shipment arrival field includes the time component, so filtering by date alone might catch records at the boundary. Also, shipments can have multiple status records in the brain, so if you're not grouping by shipment ID you could be double-counting. Here's the way to write it: use a date-range filter on arrival and group by shipment ID. Can you try that and tell me what you see?
- public_reply · Bas Vermeulen 10 Aug 2026 13:32That works, thanks. I ran the query the way you described and the numbers are consistent now. Much appreciated.
- status_change · 14 Aug 2026 23:59