Query through an error in Sql Server 2008 r2, Sql Server

Query through an error in Sql Server 2008 r2

create TABLE [dbo].[test1](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[EmpCode] [nvarchar](200) NULL,
	[CheckInCheckOutDate] [datetime] NULL,
	[WorkDate] [datetime] NULL,
	[InOutMode] [int] NULL,
 CONSTRAINT [PK_test1] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[test1] ON

GO
INSERT [dbo].[test1] ([Id], [EmpCode], [CheckInCheckOutDate], [WorkDate], [InOutMode]) VALUES (85223, N'175', CAST(0x0000A81900BAF234 AS DateTime), CAST(0x0000A81900BAF234 AS DateTime), 0)
GO
INSERT [dbo].[test1] ([Id], [EmpCode], [CheckInCheckOutDate], [WorkDate], [InOutMode]) VALUES (85832, N'175', CAST(0x0000A81900EC93AC AS DateTime), CAST(0x0000A81900EC93AC AS DateTime), 1)
GO
INSERT [dbo].[test1] ([Id], [EmpCode], [CheckInCheckOutDate], [WorkDate], [InOutMode]) VALUES (85844, N'175', CAST(0x0000A81900F0B4B4 AS DateTime), CAST(0x0000A81900F0B4B4 AS DateTime), 0)
GO
INSERT [dbo].[test1] ([Id], [EmpCode], [CheckInCheckOutDate], [WorkDate], [InOutMode]) VALUES (85863, N'175', CAST(0x0000A81900FD1574 AS DateTime), CAST(0x0000A81900FD1574 AS DateTime), 1)
GO
INSERT [dbo].[test1] ([Id], [EmpCode], [CheckInCheckOutDate], [WorkDate], [InOutMode]) VALUES (85878, N'175', CAST(0x0000A8190108B4D8 AS DateTime), CAST(0x0000A8190108B4D8 AS DateTime), 0)
GO
INSERT [dbo].[test1] ([Id], [EmpCode], [CheckInCheckOutDate], [WorkDate], [InOutMode]) VALUES (86031, N'175', CAST(0x0000A819012CA9EC AS DateTime), CAST(0x0000A819012CA9EC AS DateTime), 1)
GO
INSERT [dbo].[test1] ([Id], [EmpCode], [CheckInCheckOutDate], [WorkDate], [InOutMode]) VALUES (86038, N'175', CAST(0x0000A81901322CDC AS DateTime), CAST(0x0000A81901322CDC AS DateTime), 0)
GO
INSERT [dbo].[test1] ([Id], [EmpCode], [CheckInCheckOutDate], [WorkDate], [InOutMode]) VALUES (86039, N'175', CAST(0x0000A81901322E08 AS DateTime), CAST(0x0000A81901322E08 AS DateTime), 1)
GO
INSERT [dbo].[test1] ([Id], [EmpCode], [CheckInCheckOutDate], [WorkDate], [InOutMode]) VALUES (86142, N'175', CAST(0x0000A81901472FC4 AS DateTime), CAST(0x0000A81901472FC4 AS DateTime), 1)
GO
INSERT [dbo].[test1] ([Id], [EmpCode], [CheckInCheckOutDate], [WorkDate], [InOutMode]) VALUES (86162, N'175', CAST(0x0000A8190150A860 AS DateTime), CAST(0x0000A8190150A860 AS DateTime), 0)
GO
INSERT [dbo].[test1] ([Id], [EmpCode], [CheckInCheckOutDate], [WorkDate], [InOutMode]) VALUES (86170, N'175', CAST(0x0000A81901525EA8 AS DateTime), CAST(0x0000A81901525EA8 AS DateTime), 1)

;WITH cte AS (
  select * ,SUM(CASE WHEN InOutMode=1 THEN 0 ELSE 1 END) OVER(PARTITION BY EmpCode ORDER BY ID) s
  from test1
), cte2 AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY EmpCode, s ORDER BY id) as rn
FROM cte 
), cte3 AS (
   SELECT *, MAX(rn) OVER(PARTITION BY EmpCode, s) AS m
    FROM cte2
)
DELETE FROM cte3
WHERE rn > 1 AND rn <> m;

SELECT *
FROM test1;


run \| edit \| history \| help	0