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.

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

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.

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

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

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

shanky2704
Starting Member

6 Posts

Posted - 2015-04-19 : 14:44:26

Thanks alot. Works perfectly now.

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.

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 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?

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

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.

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