tsql Group by min and max time for same date-CodePudding

I have a following data in which each person could have event time-in and event time-out data which can be multiple times in same date as logged for Mike in below code.

DECLARE @vtable TABLE 
(
    Id Int NOT NULL, 
    Name VARCHAR(MAX) NOT NULL,
    EventDate date NOT NULL, 
    EventTime time NOT NULL
);

INSERT INTO @vtable (Id, Name, EventDate, EventTime)
VALUES (1, 'Mike', '2022-10-10', '08:00'),
       (2, 'Mike', '2022-10-10', '11:00'),
       (3, 'Mike', '2022-10-10', '12:00'),
       (4, 'Mike', '2022-10-10', '18:00'),
       (5, 'Jen', '2022-10-10', '09:00'),
       (6, 'Jen', '2022-10-10', '12:00'),
       (7, 'Jen', '2022-10-11', '14:00'),
       (8, 'Jen', '2022-10-11', '18:00')

I tried it with the following query but didn't get the required results:

SELECT 
    Name, EventDate, 
    MIN(EventTime) AS In, MAX(EventTime) AS Out 
FROM
    @vtable 
GROUP BY
    Name, EventDate

I want to get a result like this:

CodePudding user response：

A simplified solution is to use the appropriate grouping:

SELECT Name, EventDate, MIN(EventTime) AS [In], MAX(EventTime) AS [Out]
FROM (
   SELECT *, ROW_NUMBER() OVER (PARTITION BY Name, EventDate ORDER BY Id) AS Rn
   FROM @vtable
) t
GROUP BY Name, EventDate, (Rn - 1) / 2
ORDER BY Name, EventDate