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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Find time period for a type of alarm with overlaps
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shanky2704
Starting Member

6 Posts

Posted - 04/18/2015 :  16:56:33  Show Profile  Reply with Quote
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 - 04/18/2015 :  22:56:52  Show Profile  Reply with Quote
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 - 04/19/2015 :  06:49:39  Show Profile  Reply with Quote
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 - 04/19/2015 :  08:23:25  Show Profile  Reply with Quote
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 - 04/19/2015 :  10:10:23  Show Profile  Reply with Quote
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 - 04/19/2015 :  14:10:58  Show Profile  Reply with Quote
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 - 04/19/2015 :  14:44:26  Show Profile  Reply with Quote
Thanks alot. Works perfectly now.
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 04/19/2015 :  16:25:58  Show Profile  Reply with Quote
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 - 04/21/2015 :  16:08:23  Show Profile  Reply with Quote
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 - 04/21/2015 :  16:50:24  Show Profile  Reply with Quote
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 - 05/19/2015 :  09:43:29  Show Profile  Reply with Quote
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 - 05/21/2015 :  17:47:29  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000