Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
Query through an error in Sql Server 2008 r2
Language:
Ada
Assembly
Bash
C#
C++ (gcc)
C++ (clang)
C++ (vc++)
C (gcc)
C (clang)
C (vc)
Client Side
Clojure
Common Lisp
D
Elixir
Erlang
F#
Fortran
Go
Haskell
Java
Javascript
Kotlin
Lua
MySql
Node.js
Ocaml
Octave
Objective-C
Oracle
Pascal
Perl
Php
PostgreSQL
Prolog
Python
Python 3
R
Rust
Ruby
Scala
Scheme
Sql Server
Swift
Tcl
Visual Basic
Layout:
Vertical
Horizontal
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;
View schema
Execution time: 0,05 sec, rows selected: 10, rows affected: 12, absolute service time: 0,2 sec
edit mode
|
history
|
discussion
Id
EmpCode
CheckInCheckOutDate
WorkDate
InOutMode
1
85223
175
27.10.2017 11:20:39
27.10.2017 11:20:39
0
2
85832
175
27.10.2017 14:21:21
27.10.2017 14:21:21
1
3
85844
175
27.10.2017 14:36:23
27.10.2017 14:36:23
0
4
85863
175
27.10.2017 15:21:27
27.10.2017 15:21:27
1
5
85878
175
27.10.2017 16:03:46
27.10.2017 16:03:46
0
6
86031
175
27.10.2017 18:14:41
27.10.2017 18:14:41
1
7
86038
175
27.10.2017 18:34:45
27.10.2017 18:34:45
0
8
86142
175
27.10.2017 19:51:15
27.10.2017 19:51:15
1
9
86162
175
27.10.2017 20:25:44
27.10.2017 20:25:44
0
10
86170
175
27.10.2017 20:31:58
27.10.2017 20:31:58
1