I have a table My table with result like as below:
Id EmpCode CheckInCheckOutDate WorkDate InOutMode
85223 175 2017-10-27 11:20:39.000 2017-10-27 11:20:39.000 0
85832 175 2017-10-27 14:21:21.000 2017-10-27 14:21:21.000 1
85844 175 2017-10-27 14:36:23.000 2017-10-27 14:36:23.000 0
85863 175 2017-10-27 15:21:27.000 2017-10-27 15:21:27.000 1
85878 175 2017-10-27 16:03:46.000 2017-10-27 16:03:46.000 0
86031 175 2017-10-27 18:14:41.000 2017-10-27 18:14:41.000 1
86038 175 2017-10-27 18:34:45.000 2017-10-27 18:34:45.000 0
**86039 175 27.10.2017 18:34:46.000 27.10.2017 18:34:46.000 1
86142 175 2017-10-27 19:51:15.000 2017-10-27 19:51:15.000 1**
86162 175 2017-10-27 20:25:44.000 2017-10-27 20:25:44.000 0
86170 175 2017-10-27 20:31:58.000 2017-10-27 20:31:58.000 1
and i want to result like below:(after escape the one consecutive row which have same value in "InOutMode" column of consecutive rows either 0 or 1 condition has based on workdate, Example for : id is '86039' and '86142' row we have same value '1' in last column so I want to remove first row and want to have escape only old row.)
85223 175 2017-10-27 11:20:39.000 2017-10-27 11:20:39.000 0
85832 175 2017-10-27 14:21:21.000 2017-10-27 14:21:21.000 1
85844 175 2017-10-27 14:36:23.000 2017-10-27 14:36:23.000 0
85863 175 2017-10-27 15:21:27.000 2017-10-27 15:21:27.000 1
85878 175 2017-10-27 16:03:46.000 2017-10-27 16:03:46.000 0
86031 175 2017-10-27 18:14:41.000 2017-10-27 18:14:41.000 1
86038 175 2017-10-27 18:34:45.000 2017-10-27 18:34:45.000 0
86142 175 2017-10-27 19:51:15.000 2017-10-27 19:51:15.000 1
86162 175 2017-10-27 20:25:44.000 2017-10-27 20:25:44.000 0
86170 175 2017-10-27 20:31:58.000 2017-10-27 20:31:58.000 1