Hello,
I am using SQL Server 2012 Express.
I have below table structure and data
create table activity (
autoinc bigint identity(1,1) NOT NULL,
adatetime datetime2(0),
username varchar(50),
projectcode int,
action tinyint,
lat numeric(18, 8),
lon numeric(18, 8),
accuracy numeric(18, 8),
createdat datetime2(0),
constraint pk_activity PRIMARY KEY (autoinc)
);
SET IDENTITY_INSERT [dbo].[activity] ON
GO
INSERT [dbo].[activity] ([autoinc], [adatetime], [username], [projectcode], [action], [lat], [lon], [accuracy], [createdat]) VALUES (15, CAST(N'2020-12-27 19:02:19.0000000' AS DateTime2), N'ertan', 4, 0, CAST(0.41310920 AS Numeric(18, 8)), CAST(0.67578200 AS Numeric(18, 8)), CAST(0.00000000 AS Numeric(18, 8)), CAST(N'2020-12-27 19:02:23.0000000' AS DateTime2))
GO
INSERT [dbo].[activity] ([autoinc], [adatetime], [username], [projectcode], [action], [lat], [lon], [accuracy], [createdat]) VALUES (16, CAST(N'2020-12-27 19:02:44.0000000' AS DateTime2), N'ertan', 4, 1, CAST(0.41311510 AS Numeric(18, 8)), CAST(0.67578890 AS Numeric(18, 8)), CAST(0.00000000 AS Numeric(18, 8)), CAST(N'2020-12-27 19:02:47.0000000' AS DateTime2))
GO
INSERT [dbo].[activity] ([autoinc], [adatetime], [username], [projectcode], [action], [lat], [lon], [accuracy], [createdat]) VALUES (17, CAST(N'2020-12-27 19:37:56.0000000' AS DateTime2), N'ertan', 3, 0, CAST(0.41313320 AS Numeric(18, 8)), CAST(0.67576840 AS Numeric(18, 8)), CAST(0.00000000 AS Numeric(18, 8)), CAST(N'2020-12-27 19:38:00.0000000' AS DateTime2))
GO
INSERT [dbo].[activity] ([autoinc], [adatetime], [username], [projectcode], [action], [lat], [lon], [accuracy], [createdat]) VALUES (18, CAST(N'2020-12-27 19:40:53.0000000' AS DateTime2), N'ertan', 3, 1, CAST(0.41311990 AS Numeric(18, 8)), CAST(0.67577190 AS Numeric(18, 8)), CAST(0.00000000 AS Numeric(18, 8)), CAST(N'2020-12-27 19:40:58.0000000' AS DateTime2))
GO
INSERT [dbo].[activity] ([autoinc], [adatetime], [username], [projectcode], [action], [lat], [lon], [accuracy], [createdat]) VALUES (19, CAST(N'2020-12-27 19:41:01.0000000' AS DateTime2), N'ertan', 6, 0, CAST(0.41312690 AS Numeric(18, 8)), CAST(0.67577120 AS Numeric(18, 8)), CAST(0.00000000 AS Numeric(18, 8)), CAST(N'2020-12-27 19:41:18.0000000' AS DateTime2))
GO
INSERT [dbo].[activity] ([autoinc], [adatetime], [username], [projectcode], [action], [lat], [lon], [accuracy], [createdat]) VALUES (20, CAST(N'2020-12-27 19:50:00.0000000' AS DateTime2), N'ertan', 6, 1, CAST(0.41311800 AS Numeric(18, 8)), CAST(0.67578570 AS Numeric(18, 8)), CAST(0.00000000 AS Numeric(18, 8)), CAST(N'2020-12-27 19:50:05.0000000' AS DateTime2))
GO
INSERT [dbo].[activity] ([autoinc], [adatetime], [username], [projectcode], [action], [lat], [lon], [accuracy], [createdat]) VALUES (21, CAST(N'2020-12-27 19:50:20.0000000' AS DateTime2), N'ertan', 3, 0, CAST(0.41311970 AS Numeric(18, 8)), CAST(0.67578050 AS Numeric(18, 8)), CAST(0.00000000 AS Numeric(18, 8)), CAST(N'2020-12-27 19:50:24.0000000' AS DateTime2))
GO
INSERT [dbo].[activity] ([autoinc], [adatetime], [username], [projectcode], [action], [lat], [lon], [accuracy], [createdat]) VALUES (22, CAST(N'2020-12-27 19:50:28.0000000' AS DateTime2), N'ertan', 3, 1, CAST(0.41312220 AS Numeric(18, 8)), CAST(0.67578090 AS Numeric(18, 8)), CAST(0.00000000 AS Numeric(18, 8)), CAST(N'2020-12-27 19:50:45.0000000' AS DateTime2))
GO
INSERT [dbo].[activity] ([autoinc], [adatetime], [username], [projectcode], [action], [lat], [lon], [accuracy], [createdat]) VALUES (23, CAST(N'2020-12-27 19:54:38.0000000' AS DateTime2), N'ertan', 7, 0, CAST(0.41311670 AS Numeric(18, 8)), CAST(0.67578970 AS Numeric(18, 8)), CAST(0.00000000 AS Numeric(18, 8)), CAST(N'2020-12-27 19:54:43.0000000' AS DateTime2))
GO
INSERT [dbo].[activity] ([autoinc], [adatetime], [username], [projectcode], [action], [lat], [lon], [accuracy], [createdat]) VALUES (24, CAST(N'2020-12-27 19:59:21.0000000' AS DateTime2), N'ertan', 7, 1, CAST(0.41311520 AS Numeric(18, 8)), CAST(0.67579020 AS Numeric(18, 8)), CAST(0.00000000 AS Numeric(18, 8)), CAST(N'2020-12-27 19:59:26.0000000' AS DateTime2))
GO
INSERT [dbo].[activity] ([autoinc], [adatetime], [username], [projectcode], [action], [lat], [lon], [accuracy], [createdat]) VALUES (25, CAST(N'2020-12-27 20:01:29.0000000' AS DateTime2), N'ertan', 7, 0, CAST(0.41311790 AS Numeric(18, 8)), CAST(0.67578340 AS Numeric(18, 8)), CAST(0.00000000 AS Numeric(18, 8)), CAST(N'2020-12-27 20:01:34.0000000' AS DateTime2))
GO
INSERT [dbo].[activity] ([autoinc], [adatetime], [username], [projectcode], [action], [lat], [lon], [accuracy], [createdat]) VALUES (26, CAST(N'2020-12-27 20:01:51.0000000' AS DateTime2), N'ertan', 7, 1, CAST(0.41311270 AS Numeric(18, 8)), CAST(0.67578790 AS Numeric(18, 8)), CAST(0.00000000 AS Numeric(18, 8)), CAST(N'2020-12-27 20:01:54.0000000' AS DateTime2))
GO
INSERT [dbo].[activity] ([autoinc], [adatetime], [username], [projectcode], [action], [lat], [lon], [accuracy], [createdat]) VALUES (27, CAST(N'2020-12-27 20:02:01.0000000' AS DateTime2), N'ertan', 2, 0, CAST(0.41311310 AS Numeric(18, 8)), CAST(0.67578710 AS Numeric(18, 8)), CAST(0.00000000 AS Numeric(18, 8)), CAST(N'2020-12-27 20:02:05.0000000' AS DateTime2))
GO
INSERT [dbo].[activity] ([autoinc], [adatetime], [username], [projectcode], [action], [lat], [lon], [accuracy], [createdat]) VALUES (28, CAST(N'2020-12-27 20:02:07.0000000' AS DateTime2), N'ertan', 2, 1, CAST(0.41311410 AS Numeric(18, 8)), CAST(0.67578690 AS Numeric(18, 8)), CAST(0.00000000 AS Numeric(18, 8)), CAST(N'2020-12-27 20:02:27.0000000' AS DateTime2))
GO
INSERT [dbo].[activity] ([autoinc], [adatetime], [username], [projectcode], [action], [lat], [lon], [accuracy], [createdat]) VALUES (29, CAST(N'2020-12-27 20:02:34.0000000' AS DateTime2), N'ertan', 5, 0, CAST(0.41311540 AS Numeric(18, 8)), CAST(0.67578660 AS Numeric(18, 8)), CAST(0.00000000 AS Numeric(18, 8)), CAST(N'2020-12-27 20:02:37.0000000' AS DateTime2))
GO
INSERT [dbo].[activity] ([autoinc], [adatetime], [username], [projectcode], [action], [lat], [lon], [accuracy], [createdat]) VALUES (30, CAST(N'2020-12-27 20:02:38.0000000' AS DateTime2), N'ertan', 5, 1, CAST(0.41311240 AS Numeric(18, 8)), CAST(0.67578840 AS Numeric(18, 8)), CAST(0.00000000 AS Numeric(18, 8)), CAST(N'2020-12-27 20:02:58.0000000' AS DateTime2))
GO
INSERT [dbo].[activity] ([autoinc], [adatetime], [username], [projectcode], [action], [lat], [lon], [accuracy], [createdat]) VALUES (31, CAST(N'2020-12-27 20:03:43.0000000' AS DateTime2), N'ertan', 3, 0, CAST(0.41311290 AS Numeric(18, 8)), CAST(0.67578920 AS Numeric(18, 8)), CAST(0.00000000 AS Numeric(18, 8)), CAST(N'2020-12-27 20:03:47.0000000' AS DateTime2))
GO
INSERT [dbo].[activity] ([autoinc], [adatetime], [username], [projectcode], [action], [lat], [lon], [accuracy], [createdat]) VALUES (32, CAST(N'2020-12-27 20:03:51.0000000' AS DateTime2), N'ertan', 3, 1, CAST(0.41311030 AS Numeric(18, 8)), CAST(0.67579100 AS Numeric(18, 8)), CAST(0.00000000 AS Numeric(18, 8)), CAST(N'2020-12-27 20:04:07.0000000' AS DateTime2))
GO
INSERT [dbo].[activity] ([autoinc], [adatetime], [username], [projectcode], [action], [lat], [lon], [accuracy], [createdat]) VALUES (33, CAST(N'2020-12-27 20:07:35.0000000' AS DateTime2), N'ertan', 3, 0, CAST(0.41312320 AS Numeric(18, 8)), CAST(0.67577000 AS Numeric(18, 8)), CAST(0.00000000 AS Numeric(18, 8)), CAST(N'2020-12-27 20:07:39.0000000' AS DateTime2))
GO
INSERT [dbo].[activity] ([autoinc], [adatetime], [username], [projectcode], [action], [lat], [lon], [accuracy], [createdat]) VALUES (34, CAST(N'2020-12-27 20:07:43.0000000' AS DateTime2), N'ertan', 3, 1, CAST(0.41312030 AS Numeric(18, 8)), CAST(0.67577700 AS Numeric(18, 8)), CAST(0.00000000 AS Numeric(18, 8)), CAST(N'2020-12-27 20:07:59.0000000' AS DateTime2))
GO
INSERT [dbo].[activity] ([autoinc], [adatetime], [username], [projectcode], [action], [lat], [lon], [accuracy], [createdat]) VALUES (35, CAST(N'2020-12-27 20:09:22.0000000' AS DateTime2), N'ertan', 2, 0, CAST(0.41312400 AS Numeric(18, 8)), CAST(0.67577360 AS Numeric(18, 8)), CAST(0.00000000 AS Numeric(18, 8)), CAST(N'2020-12-27 20:09:27.0000000' AS DateTime2))
GO
INSERT [dbo].[activity] ([autoinc], [adatetime], [username], [projectcode], [action], [lat], [lon], [accuracy], [createdat]) VALUES (36, CAST(N'2020-12-27 20:09:34.0000000' AS DateTime2), N'ertan', 2, 1, CAST(0.41312610 AS Numeric(18, 8)), CAST(0.67577450 AS Numeric(18, 8)), CAST(0.00000000 AS Numeric(18, 8)), CAST(N'2020-12-27 20:09:39.0000000' AS DateTime2))
GO
INSERT [dbo].[activity] ([autoinc], [adatetime], [username], [projectcode], [action], [lat], [lon], [accuracy], [createdat]) VALUES (37, CAST(N'2020-12-27 20:10:07.0000000' AS DateTime2), N'ertan', 5, 0, CAST(0.41311550 AS Numeric(18, 8)), CAST(0.67578300 AS Numeric(18, 8)), CAST(0.00000000 AS Numeric(18, 8)), CAST(N'2020-12-27 20:10:11.0000000' AS DateTime2))
GO
INSERT [dbo].[activity] ([autoinc], [adatetime], [username], [projectcode], [action], [lat], [lon], [accuracy], [createdat]) VALUES (38, CAST(N'2020-12-27 20:10:13.0000000' AS DateTime2), N'ertan', 5, 1, CAST(0.41311490 AS Numeric(18, 8)), CAST(0.67578330 AS Numeric(18, 8)), CAST(0.00000000 AS Numeric(18, 8)), CAST(N'2020-12-27 20:10:32.0000000' AS DateTime2))
GO
INSERT [dbo].[activity] ([autoinc], [adatetime], [username], [projectcode], [action], [lat], [lon], [accuracy], [createdat]) VALUES (42, CAST(N'2020-12-27 21:29:46.0000000' AS DateTime2), N'ertan', 3, 0, CAST(0.41314070 AS Numeric(18, 8)), CAST(0.67576550 AS Numeric(18, 8)), CAST(0.00000000 AS Numeric(18, 8)), CAST(N'2020-12-27 21:29:50.0000000' AS DateTime2))
GO
INSERT [dbo].[activity] ([autoinc], [adatetime], [username], [projectcode], [action], [lat], [lon], [accuracy], [createdat]) VALUES (43, CAST(N'2020-12-27 21:30:14.0000000' AS DateTime2), N'ertan', 3, 1, CAST(0.41313760 AS Numeric(18, 8)), CAST(0.67576280 AS Numeric(18, 8)), CAST(0.00000000 AS Numeric(18, 8)), CAST(N'2020-12-27 21:30:16.0000000' AS DateTime2))
GO
INSERT [dbo].[activity] ([autoinc], [adatetime], [username], [projectcode], [action], [lat], [lon], [accuracy], [createdat]) VALUES (44, CAST(N'2021-01-31 13:38:00.0000000' AS DateTime2), N'ahmet', 5, 0, CAST(0.00000000 AS Numeric(18, 8)), CAST(0.00000000 AS Numeric(18, 8)), CAST(0.00000000 AS Numeric(18, 8)), CAST(N'2021-01-31 13:38:40.0000000' AS DateTime2))
GO
INSERT [dbo].[activity] ([autoinc], [adatetime], [username], [projectcode], [action], [lat], [lon], [accuracy], [createdat]) VALUES (45, CAST(N'2021-01-20 12:00:00.0000000' AS DateTime2), N'ertan', 3, 0, CAST(0.00000000 AS Numeric(18, 8)), CAST(0.00000000 AS Numeric(18, 8)), CAST(0.00000000 AS Numeric(18, 8)), CAST(N'2021-01-31 13:39:31.0000000' AS DateTime2))
GO
SET IDENTITY_INSERT [dbo].[activity] OFF
GO
action column in that table meaning
0 = in
1 = out
There are multiple users.
There maybe in without any out for some users.
What I am asked is to have in/out in a single row. Something like below
username | project code | in_autoinc | intime | out_autoinc | outtime |
---|---|---|---|---|---|
ertan | 4 | 15 | 27.12.20 19:02.19 | 16 | 27.12.20 19:02.44 |
ertan | 3 | 17 | 27.12.20 19:37.56 | 18 | 27.12.20 19:40.53 |
ertan | 6 | 19 | 27.12.20 19:41.01 | 20 | 27.12.20 19:40.53 |
ertan | 3 | 21 | 27.12.20 19:50.20 | 22 | 27.12.20 19:50.28 |
ertan | 7 | 23 | 27.12.20 19:54.38 | 24 | 27.12.20 19:59.21 |
ertan | 7 | 25 | 27.12.20 20:01.29 | 26 | 27.12.20 20:01.51 |
ertan | 2 | 27 | 27.12.20 20:02.01 | 28 | 27.12.20 20:02.07 |
ertan | 5 | 29 | 27.12.20 20:02.34 | 30 | 27.12.20 20:02.38 |
ertan | 3 | 31 | 27.12.20 20:03.43 | 32 | 27.12.20 20:03.51 |
ertan | 3 | 33 | 27.12.20 20:07.35 | 34 | 27.12.20 20:07.43 |
ertan | 2 | 35 | 27.12.20 20:09.22 | 36 | 27.12.20 20:09.34 |
ertan | 5 | 37 | 27.12.20 20:10.07 | 38 | 27.12.20 20:10.13 |
ertan | 3 | 42 | 27.12.20 21:29.46 | 43 | 27.12.20 21:30.14 |
ahmet | 5 | 44 | 31.01.21 13:38.00 | NULL | NULL |
ertan | 3 | 45 | 20.01.21 12:00.00 | NULL | NULL |
I tried to do it using cte, but results are far from close.
Any help is appreciated.
Thanks & Regards,
Ertan