1

Topic: Other variants of request, without cursor usage

Good afternoon!
There is a table about such type:
CREATE TABLE [dbo]. [SystemCHk_log] (
[id] [int] IDENTITY (1,1) NOT NULL;
[idSystem] [int] NULL;
[StatusError] [bit] NULL, - the status is included ungeared
[dtCreate] [datetime] NULL
)
Contains state-of-health data of systems it (is included/is switched off):
idsystem; StatusError; dtCreate
1; 1; 2018-02-22 16:13:42.740
1; 0; 2018-02-22 16:13:59.847
1; 1; 2018-02-22 16:14:43.620
1; 0; 2018-02-22 16:15:02.080
1; 0; 2018-02-22 16:30:00.667
1; 0; 2018-02-22 18:30:00.340
1; 0; 2018-02-22 19:00:00.613
1; 1; 2018-02-22 20:14:43.620
It will be necessary to receive sampling in which the beginning of change of the status and its end is reflected:
idSystem; StatusError; dtStart; dtEnd
1; 1; 2018-02-22 16:13:42.740; 2018-02-22 16:13:59.847
1; 0; 2018-02-22 16:13:59.847; 2018-02-22 16:14:43.620
1; 1; 2018-02-22 16:14:43.620; 2018-02-22 16:15:02.080
1; 0; 2018-02-22 16:15:02.080; 2018-02-22 20:14:43.620
1; 1; 2018-02-22 20:14:43.620;
Made so:

;WITH tq AS
(
SELECT
l. [idSystem], l. [StatusError], l. [dtCreate] as dtstart
,isnull (
(select top 1 ll.dtCreate from [SystemCHk_log] ll
where ll.idSystem=l.idSystem
and ll.dtCreate> l.dtCreate
and l. [StatusError] <> ll. [StatusError]
order by ll.dtCreate)
,getdate ()) as dtend
FROM [analysis]. [dbo]. [SystemCHk_log] l
)
SELECT dSystem, StatusError, min (dtstart) as dtstart, dtend
FROM tq
GROUP BY idSystem, StatusError, dtend
ORDER BY idSystem, dtend

That that seems I ....
Can prompt other more interesting decisions?

2

Re: Other variants of request, without cursor usage

WITH T AS (SELECT * FROM
(VALUES
(1,1, CAST (' 2018-02-22T16:13:42.740'AS DATETIME))
,(1,0, CAST (' 2018-02-22T16:13:59.847'AS DATETIME))
,(1,1, CAST (' 2018-02-22T16:14:43.620'AS DATETIME))
,(1,0, CAST (' 2018-02-22T16:15:02.080'AS DATETIME))
,(1,0, CAST (' 2018-02-22T16:30:00.667'AS DATETIME))
,(1,0, CAST (' 2018-02-22T18:30:00.340'AS DATETIME))
,(1,0, CAST (' 2018-02-22T19:00:00.613'AS DATETIME))
,(1,1, CAST (' 2018-02-22T20:14:43.620'AS DATETIME))
)t (idsystem, StatusError, dtCreate)
)
,G AS (SELECT N=ROW_NUMBER () OVER (PARTITION BY idsystem ORDER BY dtCreate)-ROW_NUMBER () OVER (PARTITION BY idsystem, StatusError ORDER BY dtCreate), * FROM T)
,S AS (SELECT idsystem, StatusError, dtStart=MIN (dtCreate) FROM G GROUP BY idsystem, N, StatusError)
SELECT idsystem, StatusError, dtStart, dtEnd=LEAD (dtStart) OVER (PARTITION BY idsystem ORDER BY dtStart)
FROM S
ORDER BY idsystem, dtStart;

3

Re: Other variants of request, without cursor usage

Yes forgot - MS SQL 2008

4

Re: Other variants of request, without cursor usage

iap wrote:

WITH T AS (SELECT * FROM
(VALUES
(1,1, CAST (' 2018-02-22T16:13:42.740'AS DATETIME))
,(1,0, CAST (' 2018-02-22T16:13:59.847'AS DATETIME))
,(1,1, CAST (' 2018-02-22T16:14:43.620'AS DATETIME))
,(1,0, CAST (' 2018-02-22T16:15:02.080'AS DATETIME))
,(1,0, CAST (' 2018-02-22T16:30:00.667'AS DATETIME))
,(1,0, CAST (' 2018-02-22T18:30:00.340'AS DATETIME))
,(1,0, CAST (' 2018-02-22T19:00:00.613'AS DATETIME))
,(1,1, CAST (' 2018-02-22T20:14:43.620'AS DATETIME))
)t (idsystem, StatusError, dtCreate)
)
,G AS (SELECT N=ROW_NUMBER () OVER (PARTITION BY idsystem ORDER BY dtCreate)-ROW_NUMBER () OVER (PARTITION BY idsystem, StatusError ORDER BY dtCreate), * FROM T)
,S AS (SELECT idsystem, StatusError, dtStart=MIN (dtCreate) FROM G GROUP BY idsystem, N, StatusError)
SELECT idsystem, StatusError, dtStart, dtEnd=LEAD (dtStart) OVER (PARTITION BY idsystem ORDER BY dtStart)
FROM S
ORDER BY idsystem, dtStart;

Beautifully, only the version of the server at me not that (

5

Re: Other variants of request, without cursor usage

mr.dfox wrote:

That that seems I ....
Can prompt other more interesting decisions?
MS SQL 2008

and what confuses you (considering the server version)?

6

Re: Other variants of request, without cursor usage

mr.dfox wrote:

it is passed...
Beautifully, only the version of the server at me not that (

Value on an adjacent line is possible,  S with itself. That LEAD () not to use.
Even it will be better, likely, not LEFT JOIN, and OUTER APPLY (SELLECT TOP (1) * FROM... WHERE... ORDER BY...) SS

7

Re: Other variants of request, without cursor usage

Value on an adjacent line can be received ,  S with itself, I wanted to tell.

8

Re: Other variants of request, without cursor usage

So?

WITH
T AS (
SELECT *
FROM (VALUES
(1,1, CAST (' 2018-02-22T16:13:42.740'AS DATETIME));
(1,0, CAST (' 2018-02-22T16:13:59.847'AS DATETIME));
(1,1, CAST (' 2018-02-22T16:14:43.620'AS DATETIME));
(1,0, CAST (' 2018-02-22T16:15:02.080'AS DATETIME));
(1,0, CAST (' 2018-02-22T16:30:00.667'AS DATETIME));
(1,0, CAST (' 2018-02-22T18:30:00.340'AS DATETIME));
(1,0, CAST (' 2018-02-22T19:00:00.613'AS DATETIME));
(1,1, CAST (' 2018-02-22T20:14:43.620'AS DATETIME))
)t (idsystem, StatusError, dtCreate)
)
select * from (
select l.idsystem, l.statuserror;
l.dtcreate, min (r.dtcreate) fin
,row_number ()
over (partition by l.idsystem, min (r.dtcreate)
order by l.dtcreate) n
from t l left join t r
on l.idsystem=r.idsystem and
l.dtcreate <r.dtcreate and
l.statuserror! =r.statuserror
group by l.idsystem, l.statuserror, l.dtcreate
)t where n=1 order by dtcreate;