How to Find summery of event in sql server

1

My Table data & required report are :

3
3694×782 188 KB

CREATE TABLE [dbo].[test](
[dates] [datetime] NULL,
[status] varchar NOT NULL
) ON [PRIMARY]
GO
INSERT [dbo].[test] ([dates], [status]) VALUES (CAST(N'2021-10-01T00:00:48.313' AS DateTime), N'Ideal')
GO
INSERT [dbo].[test] ([dates], [status]) VALUES (CAST(N'2021-10-01T00:00:09.313' AS DateTime), N'Stopped')
GO
INSERT [dbo].[test] ([dates], [status]) VALUES (CAST(N'2021-10-01T00:00:27.330' AS DateTime), N'Ideal')
GO
INSERT [dbo].[test] ([dates], [status]) VALUES (CAST(N'2021-10-01T00:00:48.313' AS DateTime), N'Ideal')
GO
INSERT [dbo].[test] ([dates], [status]) VALUES (CAST(N'2021-10-01T00:00:10.017' AS DateTime), N'Ideal')
GO
INSERT [dbo].[test] ([dates], [status]) VALUES (CAST(N'2021-10-01T00:01:19.367' AS DateTime), N'Stopped')
GO
INSERT [dbo].[test] ([dates], [status]) VALUES (CAST(N'2021-10-01T00:01:03.347' AS DateTime), N'Stopped')
GO
INSERT [dbo].[test] ([dates], [status]) VALUES (CAST(N'2021-10-01T00:00:48.313' AS DateTime), N'Ideal')
GO
INSERT [dbo].[test] ([dates], [status]) VALUES (CAST(N'2021-10-01T00:00:09.313' AS DateTime), N'Stopped')
GO
INSERT [dbo].[test] ([dates], [status]) VALUES (CAST(N'2021-10-01T00:00:45.323' AS DateTime), N'Stopped')
GO
INSERT [dbo].[test] ([dates], [status]) VALUES (CAST(N'2021-10-01T00:01:03.347' AS DateTime), N'Stopped')
GO
INSERT [dbo].[test] ([dates], [status]) VALUES (CAST(N'2021-10-01T00:00:48.313' AS DateTime), N'Stopped')
GO
INSERT [dbo].[test] ([dates], [status]) VALUES (CAST(N'2021-10-01T00:01:18.410' AS DateTime), N'Stopped')
GO
INSERT [dbo].[test] ([dates], [status]) VALUES (CAST(N'2021-10-01T00:01:19.367' AS DateTime), N'Stopped')
GO
INSERT [dbo].[test] ([dates], [status]) VALUES (CAST(N'2021-10-01T00:01:03.347' AS DateTime), N'Stopped')
GO

2

The problem is the test data has no way to distingish the order of the duplicate dates.
(Remember a table is an unordered set so the order they are listed cannot be assumed.)
The result can be obtained by adding an ID as the primary key, making sure the dates are unique etc

-- *** Test Data ***
CREATE TABLE #t
(
	ID int NOT NULL PRIMARY KEY
	,dates datetime NOT NULL
	,[status] varchar(10) NOT NULL
);
INSERT INTO #t
VALUES (1,'2021-10-01 00:00:09.313', 'Stopped')
	,(2,'2021-10-01 00:00:09.313', 'Stopped')
	,(3,'2021-10-01 00:00:10.017', 'Ideal')
	,(4,'2021-10-01 00:00:27.330', 'Ideal')
	,(5,'2021-10-01 00:00:45.323', 'Stopped')
	,(6,'2021-10-01 00:00:48.313', 'Ideal')
	,(7,'2021-10-01 00:00:48.313', 'Ideal')
	,(8,'2021-10-01 00:00:48.313', 'Stopped')
	,(9,'2021-10-01 00:00:48.313', 'Ideal')
	,(10,'2021-10-01 00:01:03.347', 'Stopped')
	,(11,'2021-10-01 00:01:03.347', 'Stopped')
	,(12,'2021-10-01 00:01:03.347', 'Stopped')
	,(13,'2021-10-01 00:01:18.410', 'Stopped')
	,(14,'2021-10-01 00:01:19.367', 'Stopped')
	,(15,'2021-10-01 00:01:19.367', 'Stopped');
-- *** End Test Data ***

WITH StatusChanges
AS
(
	SELECT ID, dates, [status]
		,CASE
			WHEN [status] = LAG([status]) OVER (ORDER BY dates, ID)
			THEN 0
			ELSE 1
		END AS StatusChange
	FROM #t
)
,Grps
AS
(
	SELECT ID, dates, [status]
		,SUM(StatusChange) OVER (ORDER BY dates, ID) AS Grp
		--,ISNULL(LEAD(dates) OVER (ORDER BY dates, ID), '9999') AS NextDate
		,LEAD(dates) OVER (ORDER BY dates, ID) AS NextDate
	FROM StatusChanges
)
SELECT
	MIN(dates) AS From_Date
	,MAX(NextDate) AS To_Date
	,[status]
FROM Grps
GROUP BY [status], Grp
ORDER BY From_Date;