Hi venkatsan,
As per your query i have create the sample .If you have any doubt Please revert me back.Below is the sample
SQL
DECLARE @tbl TABLE(
EmployeeID INT
,ClockTime DATETIME
,Direction INT
)
INSERT INTO @tbl VALUES(10,'2017-07-04 10:07:51.140 ',1)
INSERT INTO @tbl VALUES(10,'2017-07-04 12:05:56.073 ',2)
INSERT INTO @tbl VALUES(10,'2017-07-04 14:52:01.077 ',1)
INSERT INTO @tbl VALUES(11,'2017-07-04 10:46:33.340 ',1)
INSERT INTO @tbl VALUES(11,'2017-07-04 11:21:18.177 ',2)
INSERT INTO @tbl VALUES(12,'2017-07-04 10:21:18.177 ',1)
INSERT INTO @tbl VALUES(12,'2017-07-04 11:21:18.177 ',2)
INSERT INTO @tbl VALUES(12,'2017-07-04 12:21:18.177 ',1)
;with temp as(
SELECT ROW_NUMBER()OVER(PARTITION BY EmployeeID ORDER BY ClockTime DESC) as RowNumber,ClockTime,EmployeeID,Direction
FROM @tbl
)
SELECT EmployeeID,ClockTime,Direction FROM temp WHERE RowNumber=1 and Direction =1
Output
EmployeeID |
ClockTime |
Direction |
10 |
2017-04-07 14:52:01:077 |
1 |
12 |
2017-04-07 12:21:18:177 |
1 |