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

Desired output

Thanks in advance

CodePudding user response：

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;

CodePudding user response：

Similar FISRT_VALUE and LAST_VALUE solution as Charlieface's however a little more straightforward. Added the CAST of the Start and End date as date to the partition by, cleaned up using multiple dates.

DECLARE @t1 TABLE (Driver VARCHAR(50), StartDateTime DATETIME, EndDateTime DATETIME, StartPlace VARCHAR(20), EndPlace VARCHAR(20))

INSERT INTO @t1
VALUES
('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"'),
('Jim','2021-09-10 06:28:16','2021-09-10 08:28:16','    "StartPlace1"','    "EndPlace1"'),
('Jim','2021-09-10 08:28:16','2021-09-10 09:28:16','    "StartPlace2"','    "EndPlace2"'),
('Jim','2021-09-10 09:28:16','2021-09-10 10:28:16','    "StartPlace3"','    "EndPlace3"'),
('Jim','2021-09-10 10:28:16','2021-09-10 11:30:16','    "StartPlace4"','    "EndPlace4"')


SELECT DISTINCT
    Driver,
    CAST(StartDateTime AS DATE) AS 'Date',
    FIRST_VALUE(StartDateTime) OVER (PARTITION BY Driver, CAST(StartDateTime AS DATE) ORDER BY StartDateTime  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS StartDateTime,
    LAST_VALUE(EndDateTime) OVER (PARTITION BY Driver, CAST(EndDateTime AS DATE) ORDER BY EndDateTime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS EndDateTime,
    FIRST_VALUE(StartPlace) OVER (PARTITION BY Driver, CAST(StartDateTime AS DATE) ORDER BY StartDateTime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS StartDateTime,
    LAST_VALUE(EndPlace) OVER (PARTITION BY Driver, CAST(EndDateTime AS DATE) ORDER BY EndDateTime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS EndDateTime
FROM @t1