Problem in formatting result set in desired format

All we need is an easy explanation of the problem, so here it is.

I have devices which are installed in a different location with different area ID I am returning the device activity status of different areas on an hourly basis in the following format.

AreaId  |   UpdatedOn             | DeviceStatus
  1     | 2018-08-08 00:00:00.000 | Active
  1     | 2018-08-08 01:00:00.000 | Active
  1     | 2018-08-08 02:00:00.000 | Active
  2     | 2018-08-08 00:00:00.000 | Inactive
  2     | 2018-08-08 01:00:00.000 | Active
  2     | 2018-08-08 02:00:00.000 | Active
  3     | 2018-08-08 00:00:00.000 | Active
  3     | 2018-08-08 01:00:00.000 | Inactive
  3     | 2018-08-08 02:00:00.000 | Inactive

As its clearly visible that Device 1 was active throughout. Device 2 was not active from 00.00 to 01.00 Hrs
and device 3 was not active from 01.00 to 02.00 and 02.00 to 03.00 Hrs.

I want to show this data in the following format.

Status of devices for 8 August 2018 from 0:00 Hrs to 3:00 Hrs

Areaid     | Status
1          | Active
2          | Not Active between 00:00 hrs to 01:00 hrs
3          | Not Active between 01:00 hrs to 02:00 hrs and  02:00 hrs to 02:59hrs

for all the Areas.

How can I achieve this?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I’m not understand your desired output … maybe the following script will help you:

If OBJECT_ID('tempdb..#devices') IS  NOT NULL DROP TABLE #devices;

CREATE TABLE #Devices
    (deviceID int 
    , [AreaId] int
    , [UpdatedOn] datetime2(0)
    , [DeviceStatus] varchar(8))
;

INSERT INTO #Devices
    ([DeviceID],[AreaId], [UpdatedOn], [DeviceStatus])
VALUES
    (1,1, '2018-08-08 00:00:00', 'Active'),
    (1,1, '2018-08-08 01:00:00', 'Active'),
    (1,1, '2018-08-08 02:00:00', 'Active'),
    (1,2, '2018-08-08 00:00:00', 'Inactive'),
    (1,2, '2018-08-08 01:00:00', 'Active'),
    (1,2, '2018-08-08 02:00:00', 'Active'),
    (1,3, '2018-08-08 00:00:00', 'Active'),
    (1,3, '2018-08-08 01:00:00', 'Inactive'),
    (1,3, '2018-08-08 02:00:00', 'Inactive'),
    (1,3, '2018-08-08 03:00:00', 'Inactive'),
    (1,3, '2018-08-08 04:00:00', 'Active'),
    (1,3, '2018-08-08 05:00:00', 'Active'),
    (1,3, '2018-08-08 06:00:00', 'Inactive'),
    (1,3, '2018-08-08 07:00:00', 'Inactive'),
    (1,4, '2018-08-08 00:00:00', 'Inactive'),
    (1,4, '2018-08-08 01:00:00', 'Active'),
    (1,4, '2018-08-08 02:00:00', 'Inactive')
;

WITH Devices_Next AS
(
SELECT D.DeviceID
       ,D.AreaID
       ,D.updatedOn 
       ,D.DeviceStatus
      ,LEAD(D.updatedOn,1,DATEADD(HOUR,1,D.updatedOn) )
           OVER(PARTITION BY D.DeviceID,D.AreaID ORDER BY D.UpdatedOn) as next_UpdatedOn    
      ,LAG(D.updatedOn,1,NULL) OVER(PARTITION BY D.AreaID ORDER BY D.UpdatedOn ASC) as prev_UpdatedOn
      ,LAG(D.DeviceStatus,1,'N/A') OVER(PARTITION BY D.AreaID ORDER BY D.UpdatedOn ASC) as prev_DeviceStatus
FROM #Devices AS D
)
,Devices_Sum AS
(
SELECT
        D.DeviceID
       ,D.AreaID
       ,D.updatedOn        
       ,D.prev_UpdatedOn
       ,D.next_UpdatedOn
       ,D.DeviceStatus
       ,D.prev_DeviceStatus
       ,CASE WHEN DATEDIFF(MINUTE,D.prev_UpdatedOn,D.UpdatedOn)<=60 
                AND D.DeviceStatus = D.prev_DeviceStatus THEN 0 ELSE 1 END AS isGrp    
       ,SUM(/*isGrp*/
            CASE WHEN DATEDIFF(MINUTE,D.prev_UpdatedOn,D.UpdatedOn)<=60 
                AND D.DeviceStatus = D.prev_DeviceStatus THEN 0 ELSE 1 END 
          ) OVER(PARTITION By D.AreaID ORDER BY  D.UpdatedOn ROWS UNBOUNDED PRECEDING) AS sumGrp
FROM Devices_Next AS D
) 
--SELECT * FROM Devices_Grp ORDER BY deviceID,AreaId,UpdatedOn
,Devices_Grp AS
(
    SELECT  G.AreaId
        ,MIN(G.UpdatedOn) as min_UpdatedOn
        --,MAX(G.UpdatedOn) as max_updatedOn  
        ,MAX(G.next_UpdatedOn) as max_updatedOn
        ,G.DeviceStatus
    FROM Devices_Sum AS G
    GROUP BY G.AreaId,G.sumGrp,G.DeviceStatus
)

SELECT 
    D.deviceID
    ,D.AreaId
    , STUFF(OA.DeviceStatus,1,5,'') as DeviceStatus
FROM
(
    SELECT DISTINCT D.deviceID,D.AreaId
    FROM #Devices as D
)D
OUTER APPLY
(
    SELECT 
         ' and ' + G.DeviceStatus + ' ' + 
         CONVERT(VARCHAR(5),G.min_UpdatedOn,108) + ' hrs to ' + 
         CONVERT(VARCHAR(5),G.max_updatedOn,108) + ' hrs' 
    FROM Devices_Grp AS G
    WHERE G.AreaId = D.AreaId
    ORDER BY min_UpdatedOn
    FOR XML PATH('')
)OA(DeviceStatus)

output:

deviceID    AreaId  DeviceStatus
1   1   Active 00:00 hrs to 03:00 hrs
1   2   Inactive 00:00 hrs to 01:00 hrs and Active 01:00 hrs to 03:00 hrs
1   3   Active 00:00 hrs to 01:00 hrs and Inactive 01:00 hrs to 04:00 hrs and Active 04:00 hrs to 06:00 hrs and Inactive 06:00 hrs to 08:00 hrs
1   4   Inactive 00:00 hrs to 01:00 hrs and Active 01:00 hrs to 02:00 hrs and Inactive 02:00 hrs to 03:00 hrs

see here for more details

dbfiddle

Method 2

Does this fulfill your needs?

DECLARE @areas TABLE (Areaid int,
                      UpdatedOn datetime2,
                      DeviceStatus varchar(25));

INSERT INTO @areas( Areaid,
                    UpdatedOn,
                    DeviceStatus)
VALUES  (1, '2018-08-08 00:00:00.000' , 'Active'),
        (1, '2018-08-08 01:00:00.000' , 'Active'),
        (1, '2018-08-08 02:00:00.000' , 'Active'),
        (2, '2018-08-08 00:00:00.000' , 'Inactive'),
        (2, '2018-08-08 01:00:00.000' , 'Active'),
        (2, '2018-08-08 02:00:00.000' , 'Active'),
        (3, '2018-08-08 00:00:00.000' , 'Active'),
        (3, '2018-08-08 01:00:00.000' , 'Inactive'),
        (3, '2018-08-08 02:00:00.000' , 'Inactive');

DECLARE @Start DATETIME2(7);
DECLARE @End DATETIME2(7);

SET @Start = '8/8/2018 00:00:00';
SET @End = '8/8/2018 23:59:59';


-- Pre load the active areas for filtering
CREATE TABLE #temp( areaid int, 
                    status varchar(25));

INSERT INTO #temp(  areaid,
                    status)
SELECT distinct a.areaid, 
                'Active' as status
FROM(
        SELECT Areaid FROM @areas
        WHERE DeviceStatus = 'Active'  
        AND UpdatedOn BETWEEN @Start AND @End
        EXCEPT 
        SELECT distinct areaid FROM  @areas 
        where DeviceStatus = 'InActive'  
        AND UpdatedOn BETWEEN @Start AND @End
    ) as Active
INNER JOIN @areas a 
ON Active.areaid = a.Areaid


SELECT  a.Areaid,
        a.status
FROM #temp as a
UNION
select Final.areaid, left(Final.status,len(Final.status)-4)
from(
SELECT  ar.areaid, 
        (SELECT 'Not Active between ' +cast(cast(UpdatedOn as time) as varchar(5)) +' hrs to ' +cast(dateadd(hour,1,cast(UpdatedOn as time)) as varchar(5)) +'hrs and '   
        FROM @areas a
        WHERE   a.DeviceStatus = 'INACTIVE'  
        AND a.UpdatedOn BETWEEN @Start AND @End
        AND a.Areaid =ar.Areaid 
        FOR XML PATH('')) as status
FROM (
        SELECT  ar.areaid
        FROM @areas ar 
        EXCEPT 
        SELECT areaid FROM #temp) as ar
        ) as Final
ORDER BY a.areaid asc;




DROP TABLE #temp;

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply