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