Find time difference in different rows with a condition I have a table showing statuses (working, not working) for several devices (ordered by device desc, datetime desc):

rownum device     datetime                  status
--------------------------------------------
1     device_1   2018-06-27 12:10:05.443       1
2     device_1   2018-06-27 12:02:31.467       0
3     device_1   2018-06-27 11:57:24.260       1
4     device_2   2018-06-27 11:57:24.260       1
5     device_2   2018-06-26 12:57:24.260       1
6     device_3   2018-06-26 09:57:24.260       1      
7     device_3   2018-06-25 09:57:24.260       0 
8     device_3   2018-06-26 09:57:24.260       0
9     device_3   2018-06-24 09:57:24.260       1 
10    device_4   2018-06-27 09:57:24.260       0
11    device_4   2018-06-26 09:57:24.260       0


I need to get the amount of time during which a device was not available (status = 0). 
So for device_1 it would be time difference between the date it was not working (row 2) and working (row 1). 

Device_2 was always working.

Device_3 broke (row 8) and started working (row 6).

Device_4 was never working, so the amount of time would be from (row 11) to (row 10)

Query output table is to be:

device    amountoftimeinmins
----------------------------
device_x    0


I'd appreciate any help.
You don't need a pivot here. You can use LAG window function to retrieve the previous status and datetimes for each device, then compare their minutes if it goes from 0 to 0 or from 0 to 1.

IF OBJECT_ID('tempdb..#DeviceStatus') IS NOT NULL
    DROP TABLE #DeviceStatus

CREATE TABLE #DeviceStatus (
    rownum INT IDENTITY,
    device VARCHAR(10),
    datetime datetime,
    status BIT)

INSERT INTO #DeviceStatus (
    device,
    datetime,
    status)
VALUES
    ('device_1',   '2018-06-27 12:10:05.443',      1),
    ('device_1',   '2018-06-27 12:02:31.467',      0),
    ('device_1',   '2018-06-27 11:57:24.260',      1),
    ('device_2',   '2018-06-27 11:57:24.260',      1),
    ('device_2',   '2018-06-26 12:57:24.260',      1),
    ('device_3',   '2018-06-26 09:57:24.260',      1),     
    ('device_3',   '2018-06-25 09:57:24.260',      0),
    ('device_3',   '2018-06-26 09:57:24.260',      0),
    ('device_3',   '2018-06-24 09:57:24.260',      1),
    ('device_4',   '2018-06-27 09:57:24.260',      0),
    ('device_4',   '2018-06-26 09:57:24.260',      0)



;WITH LaggedStatus AS
(
    SELECT
        D.*,
        LaggedStatus = LAG(D.status) OVER (PARTITION BY D.device ORDER BY D.datetime ASC),
        LaggedDatetime = LAG(D.Datetime) OVER (PARTITION BY D.device ORDER BY D.datetime ASC)
    FROM
        #DeviceStatus AS D
),
MinutesBroken AS
(
    SELECT
        L.*,
        MinutesBroken = CASE WHEN L.LaggedStatus = 0 THEN DATEDIFF(MINUTE, L.LaggedDatetime, L.datetime) END
    FROM
        LaggedStatus AS L
)
SELECT
    M.device,
    TotalMinutesBroken = ISNULL(SUM(M.MinutesBroken), 0)
FROM
    MinutesBroken AS M
GROUP BY
    M.device


Result:

device      TotalMinutesBroken
device_1    8
device_2    0
device_3    1440
device_4    1440


Note: LAG is for version 2012+, if you have a previous one, you can simluate lag's effect with ROW_NUMBER().