Sql-server – Joining 3 Tables with Left Join

join;sql servert-sql

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.

Best Answer

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)
Related Question