以所需格式设置结果集的格式时出现问题

Question

我的设备安装在具有不同区域ID的不同位置，我每小时以以下格式返回不同区域的设备活动状态。

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 期间未处于活动状态。

我想以以下格式显示此数据。

2018 年 8 月 8 日 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

对于所有区域。

我怎样才能做到这一点？

Answer 1

您可以尝试在子查询中使用STUFF函数来获取DeviceStatus = 'Inactive'行数据，然后基于AreaId表进行outer join

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

Answer 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