Odtnhj

I've been trying to solve this but failed to do so. I need to get the duration of how long does a server has been unavailable. Here is the following data

Date | Time | Address | Status
11-14 | 6:32 | 1.1.1.1 | Down --- Count Start
11-14 | 6:34 | 1.1.1.1 | Down
11-14 | 6:54 | 1.1.1.1 | UP
11-14 | 7:20 | 1.1.1.1 | Down --- Reset Count to 1
11-14 | 7:25 | 1.1.1.1 | Down
11-14 | 7:30 | 1.1.1.1 | Up
11-14 | 7:40 | 1.1.1.1 | Down --- Reset Count to 1
11-14 | 6:35 | 2.2.2.2 | Down --- Now this is a different counter cause of different IP

I have this query

SELECT [date]
 ,[time]
 ,[address]
 ,[ms]
 ,[bytes]
 ,[ttl]
 ,[Status]
 ,COALESCE(lag([Status]) over(order by [time]),'--') [Row]
 INTO #temp
FROM
(
SELECT [date]
 ,[time]
 ,[address]
 ,[ms]
 ,[bytes]
 ,[ttl]
 ,CASE WHEN [status] = 'Success' THEN 'UP' ELSE 'DOWN' END [Status]
 FROM [ESPS].[dbo].[Ping History] p
 INNER JOIN [SuperDashboard].[dbo].[IP_Mapping] i ON i.[IP] = p.address
 WHERE [Date] = CONVERT(Date,GETDATE())
) a

SELECT [date]
 ,[time]
 ,[address]
 ,[ms]
 ,[bytes]
 ,[ttl]
 ,[Status]
FROM
(
SELECT [date]
 ,[time]
 ,[address]
 ,[ms]
 ,[bytes]
 ,[ttl]
 ,[Status]
 ,CASE WHEN [Status] != [Row] THEN 1 ELSE 0 END [row]
FROM #temp
) a WHERE [row] = 1
DROP TABLE #temp

but this is only applicable with only one address as things get mixed up when I try to add 2.2.2.2. The ideal output of this is to get the duration of downtime of a server everytime it encounters a downtime. I hope someone can help me with this or point me to the right direction atleast.

Edit 1: Expected output should be

Date | Start DownTime | End DownTime | Address
11-14 | 6:32 | 6:54 | 1.1.1.1
11-14 | 7:20 | 7:30 | 1.1.1.1
11-14 | 7:40 | | 1.1.1.1
11-14 | 6:35 | | 2.2.2.2

This is a type of "group-and-islands" problem. If you can combine all the downs and following up (if any) into a single group, then the rest is just aggregation.

And, you can do this by counting the number of UPs that occur on or after each record. This is a simple cumulative sum, and then the rest is aggregation:

select address, date, grp,
 min(case when status = 'DOWN' then time end) as startDown,
 max(case when status = 'UP' then time end) as endUp
from (select t.*,
 sum(case when status = 'UP' then 1 else 0 end) over (partition by address, date order by time desc) as grp
 from t
 ) t
group by address, date, grp;

Use lag() function

DEMO

select *,case when stat='Down' and (prevval='Up' or prevval is null) then 1 else 0 end as val from
(
 select *,lag(stat) over(partition by address order by address) as prevval
 from #temp
)A

OUTPUT:

address stat prevval val
1.1.1.1 Down 1
1.1.1.1 Up Down 0
1.1.1.1 Down Up 1
1.1.1.1 Down Down 0
1.1.1.1 Up Down 0
1.1.1.1 Down Up 1
1.1.1.1 Down Down 0
2.2.2.2 Down 1