SQLTeam.com | Weblogs | Forums

Help needed with data normalization

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

For the given data the following should work:

WITH Leads
AS
(
	SELECT username, projectcode, autoinc AS in_autoinc, adatetime AS intime
		,LEAD(autoinc) OVER (PARTITION BY username ORDER BY autoinc) AS out_autoinc
		,LEAD(adatetime) OVER (PARTITION BY username ORDER BY autoinc) AS outtime
		,[action]
	FROM dbo.activity
)
SELECT username, projectcode, in_autoinc, intime, out_autoinc, outtime
FROM Leads
WHERE [action] = 0
ORDER BY in_autoinc;
1 Like

I am not good at CTE and I barely know about window functions.

That did work just fine.

Thank you.