how to display different data sequentially date of status 1 first in sql server

Question

how to display different data sequentially date of status 1 first

result like this image

Answer 1

First creating the test data:

DECLARE @mydate TABLE
(
    date_time DATETIME,
    statusid int
)

INSERT INTO @mydate
( date_time, statusid )
VALUES
('02/25/2015 12:09:00', 0),
('02/25/2015 12:10:00', 0),
('02/25/2015 12:11:00', 0),
('02/25/2015 12:12:00', 1),
('02/25/2015 12:13:00', 1),
('02/25/2015 12:14:00', 0),
('02/25/2015 12:15:00', 0),
('02/25/2015 12:16:00', 1),
('02/25/2015 12:17:00', 1),
('02/25/2015 12:18:00', 1),
('02/25/2015 12:19:00', 1),
('02/25/2015 12:20:00', 0),
('02/25/2015 12:21:00', 0);

Lets find out what status is before each record

; WITH StatusRecs AS
(
    SELECT 
        m.date_time, 
        m.statusid, 
        LAG(m.statusid) OVER (ORDER BY date_time) AS prev_status
    FROM @mydate m
)

Now going to pull all of the status 1 records that are different from the prev status to find the beginning of each set, we are also going to pull the date of the next status = 1 record in next_start_date

,StartStatus AS
(
    SELECT 
        s.date_time, 
        s.statusid, 
        LEAD(s.date_time) OVER (ORDER BY s.date_time) AS next_start_date 
    FROM StatusRecs s
    WHERE s.statusid != ISNULL(s.prev_status, -1)
    AND s.statusid = 1
)

Now lets pull it all together to get the last 0 status record before the next status 1 record

,MyRecs AS
(
    SELECT * FROM StartStatus ss
    OUTER APPLY
    (
        SELECT TOP 1 sr.date_time AS date_time2, sr.statusid AS statusid2 
        FROM StatusRecs sr
        WHERE sr.date_time > ss.date_time 
        AND (sr.date_time < ss.next_start_date OR ss.next_start_date IS NULL)
        ORDER BY sr.date_time DESC
    ) m
)

Now we format and output the table

SELECT m.date_time, m.statusid, m.date_time2, m.statusid2, DATEDIFF(MINUTE, m.date_time, m.date_time2) AS duration  FROM MyRecs m

date_time   statusid    date_time2  statusid2   duration
2015-02-25 12:12:00.000 1   2015-02-25 12:15:00.000 0   3
2015-02-25 12:16:00.000 1   2015-02-25 12:21:00.000 0   5