SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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)

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