Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Find time period for a type of alarm with overlaps

Author  Topic 

shanky2704
Starting Member

6 Posts

Posted - 2015-04-18 : 16:56:33
I have a dataset (DATA) like this-

Store Start End Type
XXXX 02-03-2015 10:04:00 02-03-2015 10:08:00 1
XXXX 02-03-2015 10:06:00 02-03-2015 10:10:00 2
XXXX 02-03-2015 10:09:30 02-03-2015 10:12:00 1
YYYY 03-03-2015 20:04:00 03-03-2015 20:12:00 1
YYYY 03-03-2015 20:06:00 03-03-2015 20:10:00 2
YYYY 03-03-2015 20:09:00 03-03-2015 20:16:00 1
YYYY 03-03-2015 20:15:00 03-03-2015 20:18:00 2
YYYY 03-03-2015 20:17:00 03-03-2015 20:22:00 2
YYYY 03-03-2015 20:21:00 03-03-2015 20:27:00 1
The output of this file (RESULT) is-

Store Start End Mins of Type 2 only
XXXX 02-03-2015 10:04:00 02-03-2015 10:12:00 00:01:30
YYYY 02-03-2015 20:04:00 02-03-2015 20:27:00 00:05:00

So for each Store (Store is unique in the table), I am rolling up the intervals with overlaps to create a single interval.

Now, for each store, I want to find the time period for purely type 2. So if there is an overlap, type 1 has the dominance. And I want the sum of time period of whatever is left for type 2.

I have written this code but not able to address the overlap issue:

alter table [DATA] add Outage float;

update [DATA]
set Outage = DATEDIFF(SECOND,[Start],[END])

alter table [RESULT] add [Outage_Type1 (%)] float,[Outage_Type2 (%)] float;

UPDATE [RESULT]
SET [Outage_Type1 (%)]=(Select (DATEDIFF(SECOND,Min([Start]),Max([END]))) from [DATA] where ([DATA].[Type]= '1') and ([RESULT].[Site_ID] = [DATA].[Site_ID]) and ([DATA].[Start] between [RESULT].[Start] and [RESULT].[End]) and ([DATA].[END] between [RESULT].[Start] and [RESULT].[End]))
from [RESULT]

UPDATE [RESULT]
SET [Outage_Type2 (%)]=(Select (DATEDIFF(SECOND,Min([Start]),Max([END]))) from [DATA] where ([DATA].[Type]= '2') and ([RESULT].[Site_ID] = [DATA].[Site_ID]) and ([DATA].[Start] between [RESULT].[Start] and [RESULT].[End]) and ([DATA].[END] between [RESULT].[Start] and [RESULT].[End]))
from [RESULT]

UPDATE [RESULT]
SET [Outage_Type1 (%)]=0 where [Outage_Type1 (%)] is null;

UPDATE [RESULT]
SET [Outage_Type2 (%)]=0 where [Outage_Type2 (%)] is null;

alter table [RESULT] add Outage float;

update [RESULT]
set Outage = DATEDIFF(SECOND,[Start],[END])

UPDATE [RESULT]
SET [Outage_Type1 (%)]=Case when [Outage_Type1 (%)]<>0 then ([Outage_Type1 (%)]*100/Outage)
when [Outage_Type1 (%)]=0 then 0 End;

UPDATE [RESULT]
SET [Outage_Type2 (%)]=Case when [Outage_Type2 (%)]<>0 then ([Outage_Type2 (%)]*100/Outage)
when [Outage_Type2 (%)]=0 then 0 End;

alter table [RESULT] drop column outage;

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-04-18 : 22:56:52
Try this:
cte1 will add row number so we are able to join
cte2 will remove fully overlapped rows and produce new row number
cte3 will match up start and end datetime stamps
with cte1
as (select store
,[start]
,[end]
,[type]
,row_number() over(partition by store order by [start]) as rn
from [data]
)
,cte2
as (select a.store
,a.[start]
,a.[end]
,a.[type]
,row_number() over(partition by a.store order by a.[start]) as rn
from cte1 as a
left outer join cte1 as b
on b.store=a.store
and b.rn=a.rn-1
where b.[end] is null
or b.[end]<a.[end]
)
,cte3
as (select a.store
,case
when b.[end] is null
or b.[end]<=a.[start]
or a.[type]=1
then a.[start]
else b.[end]
end as [start]
,case
when c.[start] is null
or c.[start]>a.[end]
or c.[type]<>1
then a.[end]
else c.[start]
end as [end]
,a.[type]
,a.rn
from cte2 as a
left outer join cte2 as b
on b.store=a.store
and b.rn=a.rn-1
left outer join cte2 as c
on c.store=a.store
and c.rn=a.rn+1
)
select store
,min([start]) as [start]
,max([end]) as [end]
,sum(datediff(ss,[start],[end])) as outage
,sum(case when [type]=1 then datediff(ss,[start],[end]) else 0 end) as outage_type1
,sum(case when [type]=2 then datediff(ss,[start],[end]) else 0 end) as outage_type2
,sum(case when [type]=1 then datediff(ss,[start],[end]) else 0 end)*100.0/sum(datediff(ss,[start],[end])) as [outage_type1 (%)]
,sum(case when [type]=2 then datediff(ss,[start],[end]) else 0 end)*100.0/sum(datediff(ss,[start],[end])) as [outage_type2 (%)]
from cte3
group by store
order by store
Now, to see the matched up start and end datetime stamps, replace the last select with this:
select *
from cte3
Go to Top of Page

shanky2704
Starting Member

6 Posts

Posted - 2015-04-19 : 06:49:39
Thanks for your reply bitsmed. I ran the code but found an issue with overlap. For example, if my dataset is-

Store Start End Type
ZZZ 30-03-2015 14:57:00 30-03-2015 14:59:00 2
ZZZ 30-03-2015 14:57:00 30-03-2015 15:04:00 1
ZZZ 30-03-2015 14:57:00 30-03-2015 15:04:00 1
ZZZ 30-03-2015 14:57:00 30-03-2015 15:04:00 1

The output in CTE3 is-
Store Start End outage outage_type1 outage_type2
ZZZ 30-03-2015 14:57:00 30-03-2015 15:04:00 540 420 120

Here, the outage should have been 420 (difference of start and end). And Outage_type2 should be 0 as Type1 is on during that interval which has dominance.
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-04-19 : 08:23:25
Try this:
with cte1
as (select store
,[start]
,[end]
,[type]
,row_number() over(partition by store order by [start],[type]) as rn
from [data]
)
,cte2
as (select a.store
,a.[start]
,a.[end]
,a.[type]
,row_number() over(partition by a.store order by a.[start],a.[type]) as rn
from cte1 as a
left outer join cte1 as b
on b.store=a.store
and b.rn=a.rn-1
where b.[end] is null
or b.[end]<a.[end]
)
,cte3
as (select a.store
,case
when b.[end] is null
or b.[end]<=a.[start]
or a.[type]=1
then a.[start]
else b.[end]
end as [start]
,case
when c.[start] is null
or c.[start]>a.[end]
or c.[type]<>1
then a.[end]
else c.[start]
end as [end]
,a.[type]
,a.rn
from cte2 as a
left outer join cte2 as b
on b.store=a.store
and b.rn=a.rn-1
left outer join cte2 as c
on c.store=a.store
and c.rn=a.rn+1
)
select store
,min([start]) as [start]
,max([end]) as [end]
,sum(datediff(ss,[start],[end])) as outage
,sum(case when [type]=1 then datediff(ss,[start],[end]) else 0 end) as outage_type1
,sum(case when [type]=2 then datediff(ss,[start],[end]) else 0 end) as outage_type2
,sum(case when [type]=1 then datediff(ss,[start],[end]) else 0 end)*100.0/sum(datediff(ss,[start],[end])) as [outage_type1 (%)]
,sum(case when [type]=2 then datediff(ss,[start],[end]) else 0 end)*100.0/sum(datediff(ss,[start],[end])) as [outage_type2 (%)]
from cte3
group by store
order by store
Go to Top of Page

shanky2704
Starting Member

6 Posts

Posted - 2015-04-19 : 10:10:23
Hey for the following dataset-
Store Start End Type
AAA 29-03-2015 02:00:00 29-03-2015 03:24:00 1
AAA 29-03-2015 02:00:00 29-03-2015 03:24:00 1
AAA 29-03-2015 02:00:00 29-03-2015 03:24:00 1
AAA 29-03-2015 02:43:00 29-03-2015 02:46:00 2
AAA 29-03-2015 02:49:00 29-03-2015 02:52:00 2
AAA 29-03-2015 02:56:00 29-03-2015 02:59:00 2
AAA 29-03-2015 03:02:00 29-03-2015 03:06:00 2
AAA 29-03-2015 03:10:00 29-03-2015 03:13:00 2
AAA 29-03-2015 03:16:00 29-03-2015 03:19:00 2

The Outage_Type2 is coming as negative the the duration of outage is also not right-
Store Start End outage outage_type1 outage_type2
AAA 29-03-2015 02:00:00 29-03-2015 03:24:00 3900 5040 -1140

Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-04-19 : 14:10:58
Try this:
with cte1
as (select store
,[start]
,[end]
,[type]
,row_number() over(partition by store order by [start],[type]) as rn
from [data]
)
,cte2
as (select a.store
,a.[start]
,a.[end]
,a.[type]
,row_number() over(partition by a.store order by a.[start],a.[type]) as rn
from cte1 as a
left outer join cte1 as b
on b.store=a.store
and b.rn=a.rn-1
where b.[end] is null
or b.[end]<a.[end]
where not exists (select *
from cte1 as b
where b.store=a.store
and b.rn<a.rn
and b.[start]<=a.[start]
and b.[end]>=a.[end]
)

)
,cte3
as (select a.store
,case
when b.[end] is null
or b.[end]<=a.[start]
or a.[type]=1
then a.[start]
else b.[end]
end as [start]
,case
when c.[start] is null
or c.[start]>a.[end]
or c.[type]<>1
then a.[end]
else c.[start]
end as [end]
,a.[type]
,a.rn
from cte2 as a
left outer join cte2 as b
on b.store=a.store
and b.rn=a.rn-1
left outer join cte2 as c
on c.store=a.store
and c.rn=a.rn+1
)
select store
,min([start]) as [start]
,max([end]) as [end]
,sum(datediff(ss,[start],[end])) as outage
,sum(case when [type]=1 then datediff(ss,[start],[end]) else 0 end) as outage_type1
,sum(case when [type]=2 then datediff(ss,[start],[end]) else 0 end) as outage_type2
,sum(case when [type]=1 then datediff(ss,[start],[end]) else 0 end)*100.0/sum(datediff(ss,[start],[end])) as [outage_type1 (%)]
,sum(case when [type]=2 then datediff(ss,[start],[end]) else 0 end)*100.0/sum(datediff(ss,[start],[end])) as [outage_type2 (%)]
from cte3
group by store
order by store
Go to Top of Page

shanky2704
Starting Member

6 Posts

Posted - 2015-04-19 : 14:44:26
Thanks alot. Works perfectly now.
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-04-19 : 16:25:58
You're welcome.
Thinking about it, you actually can skip the whole cte1 section and let the cte2 section run directly on your table (data). This ought to speed the query up a bit.
Go to Top of Page

shanky2704
Starting Member

6 Posts

Posted - 2015-04-21 : 16:08:23
Hey bitsmed. Sorry to bother you again. I found another case where I have a conflict. For the data set-

Store Start End Type
BBB 03-03-2015 20:18:00 03-03-2015 20:23:00 2
BBB 03-03-2015 20:18:00 31-03-2015 23:59:59 2
BBB 03-03-2015 20:18:00 03-03-2015 20:23:00 2
BBB 03-03-2015 20:18:00 31-03-2015 23:59:59 2
BBB 03-03-2015 20:21:00 31-03-2015 23:59:59 1
BBB 03-03-2015 20:23:00 03-03-2015 20:23:00 2
BBB 03-03-2015 20:23:00 03-03-2015 20:25:00 2

The output is-

Store Start End outage outage_type1 outage_type2
BBB 03-03-2015 20:18:00 31-03-2015 23:59:59 2432519 0 2432519

The problem here is there is a type 1 alarm in between starting at 03-03-2015 20:21:00 and going on till 31-03-2015 23:59:59 which gets removed when CTE2 is formed (overlapped cases are removed) which I don't want as I want the precedence to be given to type 1 alarm. Is there any way we can give precedence to type 1 alarm when removing the overlapped cases?
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-04-21 : 16:50:24
Try this:
with cte1
as (select store
,[start]
,[end]
,[type]
,row_number() over(partition by store order by [start],[type],[end] desc) as rn
from [data]
)
,cte2
as (select a.store
,a.[start]
,a.[end]
,a.[type]
,row_number() over(partition by a.store order by a.[start],a.[type],a.[end] desc) as rn
from cte1 as a
where not exists (select *
from cte1 as b
where b.store=a.store
and b.rn<a.rn
and b.[start]<=a.[start]
and b.[end]>=a.[end]
and b.[type]<=a.[type]
)
)
,cte3
as (select a.store
,case
when b.[end] is null
or b.[end]<=a.[start]
or a.[type]=1
then a.[start]
else b.[end]
end as [start]
,case
when c.[start] is null
or c.[start]>a.[end]
or c.[type]<>1
then a.[end]
else c.[start]
end as [end]
,a.[type]
,a.rn
from cte2 as a
left outer join cte2 as b
on b.store=a.store
and b.rn=a.rn-1
left outer join cte2 as c
on c.store=a.store
and c.rn=a.rn+1
)
select store
,min([start]) as [start]
,max([end]) as [end]
,sum(datediff(ss,[start],[end])) as outage
,sum(case when [type]=1 then datediff(ss,[start],[end]) else 0 end) as outage_type1
,sum(case when [type]=2 then datediff(ss,[start],[end]) else 0 end) as outage_type2
,sum(case when [type]=1 then datediff(ss,[start],[end]) else 0 end)*100.0/sum(datediff(ss,[start],[end])) as [outage_type1 (%)]
,sum(case when [type]=2 then datediff(ss,[start],[end]) else 0 end)*100.0/sum(datediff(ss,[start],[end])) as [outage_type2 (%)]
from cte3
group by store
order by store
Go to Top of Page

shanky2704
Starting Member

6 Posts

Posted - 2015-05-19 : 09:43:29
Hi Bitsmed

Firstly, thanks for your help. I am facing one issue with respect to the code-
For the following dataset-
Store Start End Type
AAA 14-04-2015 11:43:00 14-04-2015 11:46:00 2
AAA 14-04-2015 11:43:00 14-04-2015 11:45:00 2
AAA 14-04-2015 11:43:00 14-04-2015 11:45:00 2
AAA 14-04-2015 11:43:00 14-04-2015 11:45:00 2
AAA 14-04-2015 11:43:00 14-04-2015 11:45:00 2
AAA 14-04-2015 11:43:00 14-04-2015 11:46:00 2
AAA 14-04-2015 11:45:00 14-04-2015 11:54:00 1
AAA 14-04-2015 11:45:00 14-04-2015 11:54:00 1
AAA 14-04-2015 11:46:00 14-04-2015 11:50:00 2
AAA 14-04-2015 11:46:00 14-04-2015 11:50:00 2
AAA 14-04-2015 11:50:00 14-04-2015 11:55:00 2
AAA 14-04-2015 11:50:00 14-04-2015 11:55:00 2
AAA 14-04-2015 11:51:00 14-04-2015 12:20:00 1
AAA 14-04-2015 11:53:00 14-04-2015 11:55:00 2
AAA 14-04-2015 11:53:00 14-04-2015 11:55:00 2
AAA 14-04-2015 11:53:00 14-04-2015 11:55:00 2
AAA 14-04-2015 11:53:00 14-04-2015 11:55:00 2
AAA 14-04-2015 11:53:00 14-04-2015 11:55:00 2
AAA 14-04-2015 11:53:00 14-04-2015 11:55:00 2
AAA 14-04-2015 11:54:00 14-04-2015 12:20:00 1
AAA 14-04-2015 11:54:00 14-04-2015 12:20:00 1

The output is coming as-
Store Start End Outage_Type2
AAA 14-04-2015 11:43:00 14-04-2015 12:20:00 -1

Whereas the attribution to Outage_Type2 should be 2 mins.
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-05-21 : 17:47:29
How about this:
with cte1
as (select store
,[start]
,[end]
,[type]
,row_number() over(partition by store order by [start],[type],[end] desc) as rn
from [data]
)
,cte2
as (select a.store
,a.[start]
,a.[end]
,a.[type]
,row_number() over(partition by a.store order by a.[start],a.[type],a.[end] desc) as rn
from cte1 as a
where not exists (select *
from cte1 as b
where b.store=a.store
and b.rn<a.rn
and b.[start]<=a.[start]
and b.[end]>=a.[end]
and b.[type]<=a.[type]
)
)
,cte3
as (select a.store
,case
when b.[end] is null
or b.[end]<=a.[start]
or a.[type]=1
then a.[start]
else b.[end]
end as [start]
,case
when c.[start] is null
or c.[start]>a.[end]
or c.[type]<>1
then a.[end]
else c.[start]
end as [end]
,a.[type]
,a.rn
from cte2 as a
left outer join cte2 as b
on b.store=a.store
and b.rn=a.rn-1
left outer join cte2 as c
on c.store=a.store
and c.rn=a.rn+1
)
select store
,min([start]) as [start]
,max([end]) as [end]
,sum(datediff(ss,[start],[end])) as outage
,sum(case when [type]=1 then datediff(ss,[start],[end]) else 0 end) as outage_type1
,sum(case when [type]=2 then datediff(ss,[start],[end]) else 0 end) as outage_type2
,sum(case when [type]=1 then datediff(ss,[start],[end]) else 0 end)*100.0/sum(datediff(ss,[start],[end])) as [outage_type1 (%)]
,sum(case when [type]=2 then datediff(ss,[start],[end]) else 0 end)*100.0/sum(datediff(ss,[start],[end])) as [outage_type2 (%)]
from cte3
where [end]>=[start]
group by store
order by store
Go to Top of Page
   

- Advertisement -