I added a small part to Jonathan's query, to include the values with no ping entry. They will have NULL The values on the OnlineStart and OnlineEnd time forare now formatted on one row aswell, 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 using XML.
"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
Create table #temp( AreaID INT
, AreaName VARCHAR(100) NOT NULL
, OnlineStart datetime2
, OnlineEnd datetime2)
/** 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
)
INSERT INTO #temp(AreaName,AreaID,OnlineStart,OnlineEnd)
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);
selectSELECT distinctDISTINCT t.AreaID,
t.AreaName,case
when CASE WHEN a.times = '' thenTHEN 'INACTIVE' else
ELSE a.times endEND fromAS Status
FROM #temp t
crossCROSS applyAPPLY(
SELECT 'ACTIVE BETWEEN' +cast(datepart(hour,OnlineStart) as varchar(10))+':'+cast(datepart(minute,OnlineStart) as varchar(010)) +' hrs to ' +cast(datepart(hour,OnlineEnd) as varchar(10))+':'+cast(datepart(MINUTE,OnlineEnd) as varchar(10)) +'hrs' as times
FROM #temp t2
whereWHERE t2.AreaID = t.AreaID
forFOR xmlXML pathPATH('')) as a(times);
DROP TABLE #temp#temp;