I have the follwoing result set from the table PUNCHREPORT
EMPLID Name PUNCHDATE PUNCHTIME DEVICETYPE
..................................................................
70 GT 2014-05-28 07:46:53.0000000 IN
70 GT 2014-05-28 07:46:58.0000000 IN
70 GT 2014-05-28 07:47:50.0000000 IN
70 GT 2014-05-28 12:16:19.0000000 OUT
70 GT 2014-05-28 12:17:55.0000000 OUT
70 GT 2014-05-28 12:24:27.0000000 IN
70 GT 2014-05-28 16:32:57.0000000 OUT
70 GT 2014-05-28 16:33:57.0000000 OUT
70 GT 2014-05-28 16:33:59.0000000 OUT
72 MS 2014-05-28 07:47:50.0000000 IN
72 MS 2014-05-28 12:16:19.0000000 OUT
88 ZE 2014-05-28 07:47:50.0000000 IN
88 ZE 2014-05-28 07:48:40.0000000 IN
88 ZE 2014-05-28 12:16:19.0000000 OUT
I need the above should be result as follow:
EMPLID Name PUNCHDATE PUNCHTIME DEVICETYPE
..................................................................
70 GT 2014-05-28 07:47:50.0000000 IN
70 GT 2014-05-28 12:17:55.0000000 OUT
70 GT 2014-05-28 12:24:27.0000000 IN
70 GT 2014-05-28 16:33:59.0000000 OUT
72 MS 2014-05-28 07:47:50.0000000 IN
72 MS 2014-05-28 12:16:19.0000000 OUT
88 ZE 2014-05-28 07:48:40.0000000 IN
88 ZE 2014-05-28 12:16:19.0000000 OUT
Means for the every occurence of DEVICETYPE
the latest row will be taken
Best Answer
To get that every row need to know the value of the next one, but the
LEAD
function is not in SQLServer 2008, so an auto-join is used in his steadSQLFiddle demo
The
Row_Number
is used to simplify theJOIN
condition.