By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE GPSpositions
([Registration] varchar(6), [Latitude] decimal(10,3), [Longitude] decimal(10,3), [dateOfRecord] datetime)
;
INSERT INTO GPSpositions
([Registration], [Latitude], [Longitude], [dateOfRecord])
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'),
('A2 XBO', 100.000, 1.000, '2019-08-01 00:05:00.000'),
('A2 XBO', 100.000, 1.001, '2019-08-01 00:06:00.000'),
('A2 XBO', 100.000, 1.002, '2019-08-01 00:21:00.000'),
('A1 XBO', 123.000, 1.400, '2019-08-02 00:01:00.000'),
('A1 XBO', 128.066, 1.436, '2019-08-02 22:04:19.000'),
('A1 XBO', 130.000, 1.456, '2019-08-02 23:00:00.000'),
('A2 XBO', 100.000, 1.008, '2019-08-02 00:06:00.000'),
('A2 XBO', 100.000, 1.009, '2019-08-02 00:06:01.000'),
('A2 XBO', 100.000, 1.010, '2019-08-02 00:23:00.000');
--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
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],
| Reg | Day | StartTime | StartLat | StartLon | EndTime | EndLat | EndLon |
|---|---|---|---|---|---|---|---|
| A1 XBO | 2019-08-01 | 00:04:19.0000000 | 123.066 | 1.456 | 23:45:00.0000000 | 118.066 | 1.456 |
| A2 XBO | 2019-08-01 | 00:05:00.0000000 | 100.000 | 1.000 | 00:21:00.0000000 | 100.000 | 1.002 |
| A1 XBO | 2019-08-02 | 00:01:00.0000000 | 123.000 | 1.400 | 23:00:00.0000000 | 130.000 | 1.456 |
| A2 XBO | 2019-08-02 | 00:06:00.0000000 | 100.000 | 1.008 | 00:23:00.0000000 | 100.000 | 1.010 |