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

Question

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 1

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;

Answer 2

You could use the APPLY operator and do something like:

DECLARE @t table
(
    Registration varchar(10)
    , Latitude decimal(6, 3)
    , Longitude decimal(6, 3)
    , dateOfRecord datetime
)

INSERT INTO @t
VALUES
('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')

SELECT DISTINCT
    Registration Reg
    , CAST(dateOfRecord AS date) [Day]
    , T_MIN.[Time] StartTime
    , T_MIN.Latitude StartLat
    , T_MIN.Longitude StartLong
    , T_MAX.[Time] EndTime
    , T_MAX.Latitude EndLat
    , T_MAX.Longitude EndLong
FROM
    @t T
    OUTER APPLY
    (
        SELECT TOP 1
            CAST(T_MIN.dateOfRecord AS time) [Time]
            , Latitude
            , Longitude
        FROM @t T_MIN
        WHERE
            T_MIN.Registration = T.Registration
            AND CAST(T_MIN.dateOfRecord AS date) =  CAST(T.dateOfRecord AS date)
        ORDER BY T_MIN.dateOfRecord
    ) T_MIN
    OUTER APPLY
    (
        SELECT TOP 1
            CAST(T_MAX.dateOfRecord AS time) [Time]
            , Latitude
            , Longitude
        FROM @t T_MAX
        WHERE
            T_MAX.Registration = T.Registration
            AND CAST(T_MAX.dateOfRecord AS date) =  CAST(T.dateOfRecord AS date)
        ORDER BY T_MAX.dateOfRecord DESC
    ) T_MAX

Edit

Based on @SMor's comment, you could also try something like:

DECLARE @t table
(
    Registration varchar(10)
    , Latitude decimal(6, 3)
    , Longitude decimal(6, 3)
    , dateOfRecord datetime
)

INSERT INTO @t
VALUES
('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')

SELECT
    Reg
    , [Day]
    , MIN([Time]) StartTime
    , MIN(Latitude) StartLat
    , MIN(Longitude) StartLong
    , MAX([Time]) EndTime
    , MAX(Latitude) EndLat
    , MAX(Longitude) EndLong
FROM
    (
        SELECT
            Registration Reg
            , CAST(dateOfRecord AS date) [Day]
            , CAST(dateOfRecord AS time) [Time]
            , Latitude
            , Longitude
            , ROW_NUMBER() OVER (PARTITION BY Registration, CAST(dateOfRecord AS date) ORDER BY dateOfRecord) Mn
            , ROW_NUMBER() OVER (PARTITION BY Registration, CAST(dateOfRecord AS date) ORDER BY dateOfRecord DESC) Mx
        FROM @t T
    ) Q
WHERE
    Mn = 1
    OR Mx = 1
GROUP BY
    Reg
    , [Day]