Return to Revisions

I added a small part to Jonathan's query, to include the values with no ping entry. The values are now formatted on one row aswell, by using XML.

      /** 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);


SELECT DISTINCT t.AreaID,
                t.AreaName,
                CASE WHEN a.times = '' THEN 'INACTIVE' 
                ELSE a.times END AS Status 
FROM #temp t 
CROSS APPLY(
SELECT 'ACTIVE BETWEEN' +cast(datepart(hour,OnlineStart) as varchar(10))+':'+cast(datepart(minute,OnlineStart) as varchar(10)) +' hrs to ' +cast(datepart(hour,OnlineEnd) as varchar(10))+':'+cast(datepart(MINUTE,OnlineEnd) as varchar(10)) +'hrs' as times   
FROM #temp t2
WHERE  t2.AreaID = t.AreaID
FOR XML PATH('')) as a(times);


DROP TABLE #temp;