Проблема с форматированием набора результатов в желаемый формат

У меня есть устройства, которые установлены в другом месте с другим идентификатором области. Я возвращаю статус активности устройства в разных областях ежечасно в следующем формате.

AreaId  |   UpdatedOn             | DeviceStatus
  1     | 2018-08-08 00:00:00.000 | Active
  1     | 2018-08-08 01:00:00.000 | Active
  1     | 2018-08-08 02:00:00.000 | Active
  2     | 2018-08-08 00:00:00.000 | Inactive
  2     | 2018-08-08 01:00:00.000 | Active
  2     | 2018-08-08 02:00:00.000 | Active
  3     | 2018-08-08 00:00:00.000 | Active
  3     | 2018-08-08 01:00:00.000 | Inactive
  3     | 2018-08-08 02:00:00.000 | Inactive

Как ясно видно, Устройство 1 было активным повсюду. Устройство 2 не было активным с 00:00 до 01:00. а устройство 3 не было активным с 01.00 до 02.00 и с 02.00 до 03.00 часов.

Я хочу показать эти данные в следующем формате.

Состояние устройств на 8 августа 2018 г. с 0:00 до 03:00.

Areaid     | Status
1          | Active
2          | Not Active between 00:00 hrs to 01:00 hrs
3          | Not Active between 01:00 hrs to 02:00 hrs and  02:00 hrs to 03:00 hrs

для всех областей.

Как я могу этого добиться?

2
0
40
2

Ответы 2

Вы можете попробовать ниже, используя функцию lag ()

ДЕМО

select id,concat('Not Active between ', format(cast(prevd as datetime),' hh:mm '), 'hrs to ', format(cast(d as datetime),' hh:mm '), 'hrs') from
(select *,
LAG (d, 1, 0) OVER (PARTITION BY id ORDER BY d) prevd,
LAG (p, 1, 0) OVER (PARTITION BY id ORDER BY d) prevp
from cte1 
)a where prevp<>'0' and p<>prevp

Выход:

id  status
1   Not Active between  01:00 hrs to  02:00 hrs
2   Not Active between  01:00 hrs to  02:00 hrs

Вы можете попробовать использовать функцию STUFF в подзапросе, чтобы получить данные строки DeviceStatus = 'Inactive', затем базу outer join на таблице AreaId

SELECT t2.AreaId, coalesce(Status,'Active') Status
FROM (
    SELECT distinct AreaId,DeviceStatus,
           STUFF((
             SELECT ' and ' + CONVERT(VARCHAR(5),UpdatedOn,108) + ' hrs' + ' to ' + CONVERT(VARCHAR(5),DATEADD(HOUR,1,UpdatedOn),108) + ' hrs'
             FROM T tt
             WHERE tt.AreaId = t1.AreaId and tt.DeviceStatus =  t1.DeviceStatus
             FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
           ,1,4,'') Status

    FROM T t1
    WHERE DeviceStatus = 'Inactive'
) t1 RIGHT JOIN 
(
    SELECT distinct AreaId
    FROM T
) t2
on t1.AreaId = t2.AreaId 

sqlfiddle

Результат

AreaId  Status
1      Active
2      00:00 hrs to 01:00 hrs
3      01:00 hrs to 02:00 hrs and 02:00 hrs to 03:00 hrs

Не совсем. Это показывает частично правильный результат. Я интегрировал ваш код из строки 508. Взгляните на dbfiddle dbfiddle.uk/…

Thugs of Hindustan 26.10.2018 11:44

также, если устройство неактивно с 1: 00–2: 00 и 2: 00–3: 00 часов, оно должно показывать неактивен с 1:00 до 3:00 часов, а не неактивен с 1:00 до 2:00 часов и неактивен с 2:00 до 3:00 часов

Thugs of Hindustan 26.10.2018 12:17

Буду признателен, если вы поможете мне в этом.

Thugs of Hindustan 26.10.2018 12:21

@ThugsofHindustan Но ваш ожидаемый результат - 01:00 hrs to 02:00 hrs and 02:00 hrs to 03:00 hrs, так что это было с 01:00 до 03:00?

D-Shih 26.10.2018 13:26

и не могли бы вы предоставить ожидаемый результат от этого dbfiddle.uk/…

D-Shih 26.10.2018 13:26

Другие вопросы по теме