1

Topic: Intervals of the status of dens of a state

Kind day all.
The help in request writing is necessary.
There is a table of dens of a state of the equipment, it is necessary to define intervals of a certain state of the equipment.
As an example the table: the Equipment, inquiry Time, the Status

DECLARE @Table Table (Form INTEGER, DateTime DATETIME, Type INTEGER)
INSERT INTO @Table
VALUES
(101, ' 20-01-2018 15:06:56.000 ', 2);
(101, ' 20-01-2018 15:06:58.000 ', 2);
(101, ' 20-01-2018 15:07:00.000 ', 3);
(101, ' 20-01-2018 15:07:02.000 ', 2);
(101, ' 20-01-2018 15:07:04.000 ', 2);
(101, ' 20-01-2018 15:07:06.000 ', 2);
(101, ' 20-01-2018 15:07:08.000 ', 4);
(101, ' 20-01-2018 15:07:10.000 ', 4);
(101, ' 20-01-2018 15:07:12.000 ', 2);
(101, ' 20-01-2018 15:07:14.000 ', 2);
(101, ' 20-01-2018 15:07:16.000 ', 2);
(101, ' 20-01-2018 15:07:18.000 ', 57);
(102, ' 20-01-2018 15:07:20.000 ', 57);
(102, ' 20-01-2018 15:07:22.000 ', 2);
(102, ' 20-01-2018 15:07:24.000 ', 25);
(102, ' 20-01-2018 15:07:26.000 ', 25);
(102, ' 20-01-2018 15:07:28.000 ', 2);
(102, ' 20-01-2018 15:07:30.000 ', 2);
(102, ' 20-01-2018 15:07:32.000 ', 0);
(102, ' 20-01-2018 15:07:34.000 ', 2)

Expected result:
Form, DateStart, DateEnd, Type
101, 20-01-2018 15:06:56.000,20-01-2018 15:06:58.000,2
101, 20-01-2018 15:07:00.000,20-01-2018 15:07:00.000,3
101, 20-01-2018 15:07:02.000,20-01-2018 15:07:06.000,2
101, 20-01-2018 15:07:08.000,20-01-2018 15:07:10.000,4
101, 20-01-2018 15:07:12.000,20-01-2018 15:07:16.000,2
101, 20-01-2018 15:07:18.000,20-01-2018 15:07:18.000,57
102, 20-01-2018 15:07:20.000,20-01-2018 15:07:20.000,57
102, 20-01-2018 15:07:22.000,20-01-2018 15:07:22.000,2
102, 20-01-2018 15:07:24.000,20-01-2018 15:07:26.000,25
102, 20-01-2018 15:07:28.000,20-01-2018 15:07:30.000,2
102, 20-01-2018 15:07:32.000,20-01-2018 15:07:32.000,0
102, 20-01-2018 15:07:34.000,20-01-2018 15:07:34.000,2
The server version: Microsoft SQL Server 2008 R2 (SP3) - 10.50.6220.0 (X64)
It is a lot of data, the cursor was to be used no.
FAQ viewed, similar found nothing.
For certain the task typical, I will be grateful for an example of request of the decision of tasks of this kind.

2

Re: Intervals of the status of dens of a state

-Dehn-;
STFF

3

Re: Intervals of the status of dens of a state

Kind  - Eh;
Thanks, did not understand how to search at first at a forum.
As a result here that turned out.

DECLARE @Table Table (Form INTEGER, DateTime DATETIME, Type INTEGER)
INSERT INTO @Table
VALUES
(101, ' 20-01-2018 15:06:56.000 ', 2);
(101, ' 20-01-2018 15:06:58.000 ', 2);
(101, ' 20-01-2018 15:07:00.000 ', 3);
(101, ' 20-01-2018 15:07:02.000 ', 2);
(101, ' 20-01-2018 15:07:04.000 ', 2);
(101, ' 20-01-2018 15:07:06.000 ', 2);
(101, ' 20-01-2018 15:07:08.000 ', 4);
(101, ' 20-01-2018 15:07:10.000 ', 4);
(101, ' 20-01-2018 15:07:12.000 ', 2);
(101, ' 20-01-2018 15:07:14.000 ', 2);
(101, ' 20-01-2018 15:07:16.000 ', 2);
(101, ' 20-01-2018 15:07:18.000 ', 57);
(102, ' 20-01-2018 15:07:20.000 ', 57);
(102, ' 20-01-2018 15:07:22.000 ', 2);
(102, ' 20-01-2018 15:07:24.000 ', 25);
(102, ' 20-01-2018 15:07:26.000 ', 25);
(102, ' 20-01-2018 15:07:28.000 ', 2);
(102, ' 20-01-2018 15:07:30.000 ', 2);
(102, ' 20-01-2018 15:07:32.000 ', 0);
(102, ' 20-01-2018 15:07:34.000 ', 2)
--SELECT * FROM @Table
--SELECT @@ VERSION
SELECT
[Form]
,min ([DateTime]) AS DateStart
,max ([DateTime]) AS DateEnd
,[Type]
FROM (
SELECT
*
,ROW_NUMBER () OVER (PARTITION BY [Form], [Type] ORDER BY [DateTime]) - ROW_NUMBER () OVER (PARTITION BY [Form] ORDER BY [DateTime]) AS grp_id
FROM @Table
) v
GROUP BY [Form], [Type], grp_id
ORDER BY 1,2

On the test data fulfilled correctly.
I will familiarize with window functions more in detail.