Hello all:
I am working on a report that returns required vehicle service information. The data source is an SQL Stored Procedure that I cannot modify.
Some services are due by dates, while others are due by an odometer reading. Vehicles can have either or both in their service profile. From the source data, a record describing a meter-based service has a fictitious service due date of 1/1/1900.
I need to group records by due date to list soonest due (or overdue) at the top of the report, yet keep all vehicle records together, so the same vehicle doesn't show up in multiple places throughout the report.
Is there a way to accomplish this through creative grouping or perhaps creating a local variable that, for the purpose of sorting, assigns a legitimate due date (to replace the 1/1/1900) from the earliest of this vehicle's date-based records?
Here's some sample data (apologize for the column shift). Notice transposed dates in required sort.
SOURCE DATA
VehNo SvcCode Type DateDue MeterDue
123 A Date 11/10/2022 0
123 B Date 2/15/2023 0
123 A Meter 1/1/1900 26,750
456 A Date 11/5/2022 0
456 B Date 4/18/2023 0
456 B Meter 1/1/1900 83,256
REQUIRED SORT
VehNo SvcCode Type DateDue MeterDue
456 A Date 11/5/2022 0
456 B Meter 11/5/2022 83,256
456 B Date 4/18/2023 0
123 A Date 11/10/2022 0
123 A Meter 11/10/2022 26,750
123 B Date 2/15/2023 0
