I added a small part to Jonathan's query, to include the values with no ping entry. They will have NULL values on the OnlineStart and OnlineEnd time for now, otherwise we will need to either change that to a date that will not occur or a different data type. What do you mean by > "The onlineStart and onlineEnd values are coming same with your > answer" Query (all credit to Jonathan Fite): /** Build up Sample Data **/ SET NOCOUNT ON DECLARE @Area TABLE ( AreaID INT NOT NULL PRIMARY KEY IDENTITY(1,1) , AreaName VARCHAR(100) NOT NULL , IsActive BIT NOT NULL ) INSERT INTO @Area (AreaName, IsActive) VALUES ('Jumbo House', 1) , ('DCC', 1) , ('Dubai Mall', 1) , ('Old_Area',0) DECLARE @PingTable TABLE ( PingTableID INT NOT NULL PRIMARY KEY IDENTITY(1,1) , AreaID INT NOT NULL --REFERENCES @Area (AreaID) , UpdatedOn DATETIME2(7) NOT NULL ) DECLARE @Start DATETIME2(7) DECLARE @End DATETIME2(7) SET @Start = '8/8/2018 00:00:00' SET @End = '8/8/2018 23:59:59' WHILE @Start < @End BEGIN --Jumbo House INSERT INTO @PingTable (AreaID, UpdatedOn) VALUES (1, @Start) --DCC, only active after 23:00 hours. IF @Start > '8/8/2018 23:00:00' BEGIN INSERT INTO @PingTable (AreaID, UpdatedOn) VALUES (2, @Start) END --Dubai Mall, inactive between 10 AM and 12:30 PM IF NOT(@Start BETWEEN '8/8/2018 10:00:00' AND '8/8/2018 12:30:00') BEGIN INSERT INTO @PingTable (AreaID, UpdatedOn) VALUES (3, @Start) END SET @Start = DATEADD(MINUTE, 5, @Start) END /** Buil up Query **/ --DECLARE @Start DATETIME2(7) --DECLARE @End DATETIME2(7) SET @Start = '8/8/2018 00:00:00' SET @End = '8/8/2018 23:59:59' ;WITH CTE_Time AS ( SELECT 0 AS RN , DATEADD(MINUTE, 0, @Start) AS TimeToUse UNION ALL SELECT RN + 1 AS RN , DATEADD(MINUTE, RN + 1, @Start) AS TimeToUse FROM CTE_Time WHERE DATEADD(MINUTE, RN + 1, @Start) <= @End ) , CTE_LagAndLead AS ( SELECT A.AreaName , A.AreaID , P.UpdatedOn , PriorUpdateOn = LAG(P.UpdatedOn) OVER (PARTITION BY A.AreaID ORDER BY P.UpdatedOn) , NextUpdatedOn = LEAD(P.UpdatedOn) OVER (PARTITION BY A.AreaID ORDER BY P.UpdatedOn) , DR = DENSE_RANK() OVER (PARTITION BY A.AreaID ORDER BY P.UpdatedOn) FROM @Area AS A INNER JOIN @PingTable AS P ON P.AreaID = A.AreaID WHERE P.UpdatedOn BETWEEN @Start AND @End ) , CTE_Islands AS ( SELECT LL.AreaName , LL.AreaID , LL.UpdatedOn , GroupDR = DATEADD(MINUTE, -5 * DR, LL.UpdatedOn) FROM CTE_LagAndLead AS LL ) , CTE_Islands2 AS ( SELECT AreaName , AreaID , MIN(Updatedon) AS OnlineStart , MAX(Updatedon) AS OnlineEnd FROM CTE_Islands GROUP BY AreaName, AreaID, GroupDR ) SELECT AreaName , AreaID , OnlineStart , OnlineEnd FROM CTE_Islands2 UNION SELECT A.AreaName,A.AreaID, NULL as ONLINESTART, NULL As OnlineEnd FROM (SELECT a.AreaID FROM @Area AS A EXCEPT SELECT P.areaid FROM @PingTable AS P WHERE P.UpdatedOn BETWEEN @Start AND @End) as Inactive INNER JOIN @Area A on Inactive.AreaID =A.AreaID ORDER BY AreaID , OnlineStart OPTION (MAXRECURSION 0);