Find time period for a type of alarm with overlaps

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

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

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

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

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

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.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources