Return to Revisions

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
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 from #temp t 
cross apply(
SELECT 'ACTIVE BETWEEN' +cast(datepart(hour,OnlineStart) as varchar(10))+':'+cast(datepart(minute,OnlineStart) as varchar(0)) +' 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