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.