Query that returns date of first and last record for each day within a d

I have a table that records vehicle locations and I wish to query this to get the first and the last record for each vehicle for each day in a date range. The table looks like:

Registration Latitude Longitude dateOfRecord
A1 XBO        123.066 1.456     2019-08-01 00:04:19.000
A1 XBO        128.066 1.436     2019-08-01 22:04:19.000
A1 XBO        118.066 1.456      2019-08-01 23:45:00.000

There are multiple vehicles with three weeks worth of data being held in the table 100,000 records this is written to an archive every night which leaves a 21 days of records which I wish to query. With my sample I would like to get:

Reg     Day     StartTime StartLat  StartLong EndTime  EndLat EndLong
A2 XBO  01-08-19 00:04     123.066   1.456     23:45   118.066 1.456

I have an existing query that gets the most recent records but this can’t be used for my requirements as it uses the MAX(ID) within the query and I don’t believe that you can mix both MAX and MIN in the same query. I could use this as the basis of a table in a stored procedure and then loop through the records and query each to get the first record in the date range but this would be a very resource greedy process! I have included this purely to show what I already have:

SELECT        TOP (100) PERCENT m.Registration, m.Location, m.dateoffix, 
m.Latitude, m.Longitude, MAX(m.ID) AS ID
FROM            dbo.GPSPositions AS m 
                     INNER JOIN
                         (SELECT        Registration AS vr, 
MAX(CONVERT(datetime, dateoffix, 103)) AS tdate
                           FROM            dbo.GPSPositions
                           GROUP BY Registration) AS s ON m.Registration = 
s.vr AND CONVERT(datetime, m.dateoffix, 103) = s.tdate
GROUP BY m.Registration, m.Location, m.dateoffix, m.Latitude, m.Longitude                           
ORDER BY m.Registration

Answer

You can mix Max and Min in the same query.

with firstLast (Registration, firstRec, lastRec) as
(
select [Registration], min([dateOfRecord]) as firstRec, max(dateOfRecord) as lastRec
from GPSPositions
group by [Registration], cast(dateOfRecord as Date)
)
select 
   fl.Registration as Reg,
   Cast(gpsF.dateOfRecord as Date) as [Day],
   Cast(gpsF.dateOfRecord as Time) as [StartTime],
   gpsF.Latitude as StartLat,
   gpsF.Longitude as StartLon,
   Cast(gpsL.dateOfRecord as Time) as [EndTime],
   gpsL.Latitude as EndLat,
   gpsL.Longitude as EndLon
from firstLast fl
inner join GPSPositions gpsF on gpsF.Registration = fl.Registration and gpsF.dateOfRecord = fl.firstRec
inner join GPSPositions gpsL on gpsL.Registration = fl.Registration and gpsL.dateOfRecord = fl.lastRec;

Here is DBFiddle demo.

EDIT: If there could be entries for the same registration at the same time (ID is unique and increasing – ordered by dateOfRecord):

with firstLast (registration,firstRec, lastRec) as
(
select registration,min(id) as firstRec, max(id) as lastRec
from GPSPositions
group by [Registration], cast(dateOfRecord as Date)
)
select 
   fl.Registration as Reg,
   Cast(gpsF.dateOfRecord as Date) as [Day],
   Cast(gpsF.dateOfRecord as Time) as [StartTime],
   gpsF.Latitude as StartLat,
   gpsF.Longitude as StartLon,
   Cast(gpsL.dateOfRecord as Time) as [EndTime],
   gpsL.Latitude as EndLat,
   gpsL.Longitude as EndLon
from firstLast fl
inner join GPSPositions gpsF on gpsF.Id = fl.firstRec
inner join GPSPositions gpsL on gpsL.ID = fl.lastRec;