Joining 3 Tables with Left Join

Question

I have 2 Tables namely Area_ Table and PingData_Table

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

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.

what are the criteria for a device to be active and vice versa? — Biju jose, Commented Oct 25, 2018 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 — psyborg.eth, Commented Oct 25, 2018 at 9:30
Yes, It's on an hourly basis. I have to generate a report for a particular time range on daily basis. — psyborg.eth, Commented Oct 25, 2018 at 9:42

Jonathan Fite · Accepted Answer · 2018-10-25 13:35:33Z

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/… — psyborg.eth, Commented Oct 26, 2018 at 5:40

Randi Vertongen · Accepted Answer · 2018-10-26 08:15:07Z

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;

That onlineStart and onlineEnd value are the same in each row. moreover, this is not the desired result set. Please have a look at my other question dba.stackexchange.com/questions/221066/… — psyborg.eth, Commented Oct 26, 2018 at 7:58

Stack Exchange Network

Joining 3 Tables with Left Join

2 Answers 2

Your Answer

Not the answer you're looking for? Browse other questions tagged
sql-server
t-sql
join
or ask your own question.

Linked

Hot Network Questions

Joining 3 Tables with Left Join

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

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

Linked

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
sql-server
t-sql
join
or ask your own question.