Sql-server – Problem in formatting result set in desired format

sql servert-sql

I have devices which are installed in a different location with different area ID I am returning the device activity status of different areas on an hourly basis in the following format.

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

As its clearly visible that Device 1 was active throughout. Device 2 was not active from 00.00 to 01.00 Hrs
and device 3 was not active from 01.00 to 02.00 and 02.00 to 03.00 Hrs.

I want to show this data in the following format.

Status of devices for 8 August 2018 from 0:00 Hrs to 3:00 Hrs

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 02:59hrs

for all the Areas.

How can I achieve this?

Best Answer

I'm not understand your desired output ... maybe the following script will help you:

If OBJECT_ID('tempdb..#devices') IS  NOT NULL DROP TABLE #devices;

CREATE TABLE #Devices
    (deviceID int 
    , [AreaId] int
    , [UpdatedOn] datetime2(0)
    , [DeviceStatus] varchar(8))
;

INSERT INTO #Devices
    ([DeviceID],[AreaId], [UpdatedOn], [DeviceStatus])
VALUES
    (1,1, '2018-08-08 00:00:00', 'Active'),
    (1,1, '2018-08-08 01:00:00', 'Active'),
    (1,1, '2018-08-08 02:00:00', 'Active'),
    (1,2, '2018-08-08 00:00:00', 'Inactive'),
    (1,2, '2018-08-08 01:00:00', 'Active'),
    (1,2, '2018-08-08 02:00:00', 'Active'),
    (1,3, '2018-08-08 00:00:00', 'Active'),
    (1,3, '2018-08-08 01:00:00', 'Inactive'),
    (1,3, '2018-08-08 02:00:00', 'Inactive'),
    (1,3, '2018-08-08 03:00:00', 'Inactive'),
    (1,3, '2018-08-08 04:00:00', 'Active'),
    (1,3, '2018-08-08 05:00:00', 'Active'),
    (1,3, '2018-08-08 06:00:00', 'Inactive'),
    (1,3, '2018-08-08 07:00:00', 'Inactive'),
    (1,4, '2018-08-08 00:00:00', 'Inactive'),
    (1,4, '2018-08-08 01:00:00', 'Active'),
    (1,4, '2018-08-08 02:00:00', 'Inactive')
;

WITH Devices_Next AS
(
SELECT D.DeviceID
       ,D.AreaID
       ,D.updatedOn 
       ,D.DeviceStatus
      ,LEAD(D.updatedOn,1,DATEADD(HOUR,1,D.updatedOn) )
           OVER(PARTITION BY D.DeviceID,D.AreaID ORDER BY D.UpdatedOn) as next_UpdatedOn    
      ,LAG(D.updatedOn,1,NULL) OVER(PARTITION BY D.AreaID ORDER BY D.UpdatedOn ASC) as prev_UpdatedOn
      ,LAG(D.DeviceStatus,1,'N/A') OVER(PARTITION BY D.AreaID ORDER BY D.UpdatedOn ASC) as prev_DeviceStatus
FROM #Devices AS D
)
,Devices_Sum AS
(
SELECT
        D.DeviceID
       ,D.AreaID
       ,D.updatedOn        
       ,D.prev_UpdatedOn
       ,D.next_UpdatedOn
       ,D.DeviceStatus
       ,D.prev_DeviceStatus
       ,CASE WHEN DATEDIFF(MINUTE,D.prev_UpdatedOn,D.UpdatedOn)<=60 
                AND D.DeviceStatus = D.prev_DeviceStatus THEN 0 ELSE 1 END AS isGrp    
       ,SUM(/*isGrp*/
            CASE WHEN DATEDIFF(MINUTE,D.prev_UpdatedOn,D.UpdatedOn)<=60 
                AND D.DeviceStatus = D.prev_DeviceStatus THEN 0 ELSE 1 END 
          ) OVER(PARTITION By D.AreaID ORDER BY  D.UpdatedOn ROWS UNBOUNDED PRECEDING) AS sumGrp
FROM Devices_Next AS D
) 
--SELECT * FROM Devices_Grp ORDER BY deviceID,AreaId,UpdatedOn
,Devices_Grp AS
(
    SELECT  G.AreaId
        ,MIN(G.UpdatedOn) as min_UpdatedOn
        --,MAX(G.UpdatedOn) as max_updatedOn  
        ,MAX(G.next_UpdatedOn) as max_updatedOn
        ,G.DeviceStatus
    FROM Devices_Sum AS G
    GROUP BY G.AreaId,G.sumGrp,G.DeviceStatus
)

SELECT 
    D.deviceID
    ,D.AreaId
    , STUFF(OA.DeviceStatus,1,5,'') as DeviceStatus
FROM
(
    SELECT DISTINCT D.deviceID,D.AreaId
    FROM #Devices as D
)D
OUTER APPLY
(
    SELECT 
         ' and ' + G.DeviceStatus + ' ' + 
         CONVERT(VARCHAR(5),G.min_UpdatedOn,108) + ' hrs to ' + 
         CONVERT(VARCHAR(5),G.max_updatedOn,108) + ' hrs' 
    FROM Devices_Grp AS G
    WHERE G.AreaId = D.AreaId
    ORDER BY min_UpdatedOn
    FOR XML PATH('')
)OA(DeviceStatus)

output:

deviceID    AreaId  DeviceStatus
1   1   Active 00:00 hrs to 03:00 hrs
1   2   Inactive 00:00 hrs to 01:00 hrs and Active 01:00 hrs to 03:00 hrs
1   3   Active 00:00 hrs to 01:00 hrs and Inactive 01:00 hrs to 04:00 hrs and Active 04:00 hrs to 06:00 hrs and Inactive 06:00 hrs to 08:00 hrs
1   4   Inactive 00:00 hrs to 01:00 hrs and Active 01:00 hrs to 02:00 hrs and Inactive 02:00 hrs to 03:00 hrs

see here for more details

dbfiddle