0

I have 2 Tables namely Area_ Table and PingData_Table

  1. Area Table has the list of the areas along with the Area ID and Area Name
 AreaId | AreaName |  isActive
   1    |Jumbo House| 1
   2    | DCC       | 1
   3    |Dubai Mall | 1
  1. Ping Table has the ping made by devices installed on different area location with a different ID. I ping each device every 5 mins and update the status in the table.

Please note that no row is inserted in the table if the ping is unsuccessful which means the device is inactive

pkey  | AreaId  |   UpdatedOn
411   |   1     | 2018-08-08 21:54:24.810
547   |   1     | 2018-08-08 10:39:21.987
1046  |   1     | 2018-08-08 00:09:57.843

Now I want to show the above data in the following format.

Status of devices for 8 August 2018

Areaid | Name           |   Status
1      |Jumbo House     | Active
2      |DCC             | Not Active between 00:00 hrs to 23:00 hrs
3      |Dubai Mall      | Not Active between XX:00 hrs to XX:00 hrs

for all the Areas.

What I have tried till now is:

CREATE TABLE #TimeTable (hrs DATETIME)
CREATE TABLE #pingData (pid INT , areaId INT , pingTime DATETIME)
DECLARE 
  @start DATETIME = '2018-08-08 0:00', 
  @end   DATETIME =  '2018-08-08 23:00';
WITH x(n) AS 
(
  SELECT TOP (DATEDIFF(HOUR, @start, @end) + 1) 
  rn = ROW_NUMBER() OVER (ORDER BY [object_id]) 
  FROM sys.all_columns ORDER BY [object_id]
)
INSERT INTO #TimeTable(hrs) SELECT t = DATEADD(HOUR, n-1, @start) FROM x ORDER BY t;
INSERT INTO #pingData ( areaId  , pingTime ) 
SELECT DISTINCT [AreaId],CAST(FORMAT([UpdatedOn],'yyyy-MM-dd HH')+':00:00.000' as datetime) as pingtime 
FROM [dbo].[Pingtable] 
where UpdatedOn > @start and UpdatedOn <= @end
select  a.areaId , p.pingTime,t.hrs , a.AreaName , CASE WHEN  p.pingTime IS NULL THEN 'INACTIVE' ELSE 'ACTIVE' END as DeviceStatus
from Area_Table a   
left  join #pingData p on a.areaId = p.AreaId
left   join #TimeTable t  on p.pingTime = t.hrs 
ORDER BY a.areaId,t.hrs
drop table #TimeTable ;
drop table #pingData ;

Please guide me as to how to get the desired result.

  • 1
    what are the criteria for a device to be active and vice versa? – Biju jose Oct 25 '18 at 9:19
  • If there exists a row entry in ping table than a device will be active. For eg. If there is a row entry in ping table corresponding to AreaID on 10:39 PM . Device will be treated active from 10PM to 11PM and vice versa . If the device was active for all the hours in the time range than the device will be treated active for the day . Otherwise I have to show the time range when it was inactive – Shivank Verma Oct 25 '18 at 9:30
  • so your analysis is hourly right – Biju jose Oct 25 '18 at 9:34
  • Yes, It's on an hourly basis. I have to generate a report for a particular time range on daily basis. – Shivank Verma Oct 25 '18 at 9:42
1

I ran out of time on this, but I'm posting this here in case someone wants to extend or use my work so far. The best I could do was find the time(s) that the area was online. At the moment, there are a few extraneous steps in there, but this should get you close.

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

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
ORDER BY AreaID
    , OnlineStart
OPTION (MAXRECURSION 0)
  • The onlineStart and onlineEnd values are coming same with your answer. also the location for which there's no ping entry are not displayed . Please have a look at Fiddle dbfiddle.uk/… – Shivank Verma Oct 26 '18 at 5:40
0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.