SQL Getting a value from a column where another value is MIN value

Question

I am not that experienced in SQL and I am trying to get a value from a row where another value is the min value. I tried several things and can't find the answer on this forum.

My SQL:

select  
   Name, 
   MIN(StartDateAndTime) as StartTime,
   MAX(EndDateAndTime) as EndTime
from   tblWorkingTimes 
group by Name, 
         convert(date, StartDateAndTime)

So I have a table with all actions a driver has done during the day (Driving, resting, loading,...) and I want to make a summary per day. Each action also gives a StartPositionText and EndPositionText. (Example "ThisStreet 666, 9999 City")

The above SQL works but i want to add a StartPositionText and EndPositionText. So i need to get StartPositionText from row where w.StartDateAndTime = MIN(w.StartDateAndTime) and EndPositionText from row where w.EndDateAndTime = Max(w.EndDateAndTime). I tried a SELECT but I can't have MIN(w.StartDateAndTime) in a WHERE clause and I can't get it to work with a HAVING clause.

EDIT: sample data tblWorkingTimes

Name	StartDateAndTime	EndDateAndTime	StartText	EndText
Jim	2021-09-09 07:28:16	2021-09-09 08:28:16	"StartPlace1"	"EndPlace1"
Jim	2021-09-09 08:28:16	2021-09-09 09:28:16	"StartPlace2"	"EndPlace2"
Jim	2021-09-09 09:28:16	2021-09-09 10:28:16	"StartPlace3"	"EndPlace3"
Jim	2021-09-09 10:28:16	2021-09-09 11:28:16	"StartPlace4"	"EndPlace4"

Desired output

Name	StartDateAndTime	EndDateAndTime	StartText	EndText
Jim	2021-09-09 07:28:16	2021-09-09 11:28:16	"StartPlace1"	"EndPlace4"

Thanks in advance

Answer 1

You can use FIRST_VALUE and LAST_VALUE for this

select  
   w.DriverName, 
   convert(date, w.StartDateAndTime) as DayPerformed,
   MIN(w.StartDateAndTime) as StartTime,
   MAX(w.EndDateAndTime) as EndTime
   StartPositionText,
   EndPositionText
from (
    SELECT *,
      StartPositionText = FIRST_VALUE(StartPositionText) OVER (PARTITION BY w.DriverName, convert(date, w.StartDateAndTime)
            ORDER BY w.StartDateAndTime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
      EndPositionText   = LAST_VALUE(EndPositionText) OVER (PARTITION BY w.DriverName, convert(date, w.StartDateAndTime)
            ORDER BY w.StartDateAndTime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
    FROM webfleet.tblWorkingTimes w
) w
group by w.DriverName, 
         convert(date, w.StartDateAndTime),
         StartPositionText,
         EndPositionText;