Dummy table
DECLARE @tb TABLE(UserId INT, InOutDateTime DATETIME , AttendanceType CHAR(1))
INSERT INTO @tb VALUES (1,'2017-08-18 08:00:00.000','I')
INSERT INTO @tb VALUES (1,'2017-08-18 07:00:00.000','I')
INSERT INTO @tb VALUES (1,'2017-08-18 11:00:00.000','O')
INSERT INTO @tb VALUES (1,'2017-08-18 23:00:00.000','I')
INSERT INTO @tb VALUES (1,'2017-09-18 06:00:00.000','I')
INSERT INTO @tb VALUES (1,'2017-09-18 22:00:00.000','O')
INSERT INTO @tb VALUES (1,'2017-09-18 19:00:00.000','I')
INSERT INTO @tb VALUES (3,'2017-08-18 09:00:00.000','I')
INSERT INTO @tb VALUES (3,'2017-08-18 15:00:00.000','O')
INSERT INTO @tb VALUES (3,'2017-08-18 13:00:00.000','O')
INSERT INTO @tb VALUES (3,'2017-08-18 23:00:00.000','I')
select * from @tb
I need minimum InOutDateTime and maximum InOutDateTime for each user for each date. Minimum InOutDateTime should be from where AttendanceType='I' .
Similarly maximum InOutDateTime should be from where AttendanceType='O'
What I have tried:
select UserId, min(InOutDateTime) min_InDateTime, max(InOutDateTime) max_OutDateTime
from @tb group by UserId, CAST(InOutDateTime AS DATE)
ORDER BY UserId, CAST(InOutDateTime AS DATE)
But i am not able to get the results based on AttendanceType