SQL Query - Grouping consecutive elements based on a condition

Question

I do not know what to call this question. Please forgive the title if it does not correspond to the question. I have data on production and unplanned maintenance activities ordered by Equipment and then Moment. See the table below (see the bottom of the question for the text version):

Data should be grouped according to the specified colors, and the Duration column is summed up. Below is the result. Basically, unscheduled maintenance hours should be summed up with production hours until a new unplanned maintenance appears (thus grouping).

- , RANK() , , 500 000 . -, 5 . : lead lag, , , SQL (SQL Server 2008)

:

.

:

https://drive.google.com/file/d/0B8xKLs3osIfcVGRCVGJMQnBYWXc/view?usp=sharing

StartDate = Moment

EndDate

+---------------------+-----------+-----------+-------------------------+
| Moment              | Duration  | Equipment | DowntimeType            |
+---------------------+-----------+-----------+-------------------------+
| 2014-07-14 08:34:03 | 2.734444  | DT46      | Production              |
+---------------------+-----------+-----------+-------------------------+
| 2014-07-14 11:39:26 | 0.015833  | DT46      | Production              |
+---------------------+-----------+-----------+-------------------------+
| 2014-07-14 11:41:23 | 0.4925    | DT46      | Production              |
+---------------------+-----------+-----------+-------------------------+
| 2014-07-14 12:10:56 | 0.679444  | DT46      | Unscheduled Maintenance |
+---------------------+-----------+-----------+-------------------------+
| 2014-07-14 12:51:42 | 0.628888  | DT46      | Production              |
+---------------------+-----------+-----------+-------------------------+
| 2014-07-14 15:23:48 | 0.650833  | DT46      | Production              |
+---------------------+-----------+-----------+-------------------------+
| 2014-07-14 16:05:19 | 3.341111  | DT46      | Production              |
+---------------------+-----------+-----------+-------------------------+
| 2014-07-14 19:44:01 | 7.292777  | DT46      | Production              |
+---------------------+-----------+-----------+-------------------------+
| 2014-07-15 03:18:15 | 5.954722  | DT46      | Production              |
+---------------------+-----------+-----------+-------------------------+
| 2014-07-15 09:50:54 | 3.899722  | DT46      | Production              |
+---------------------+-----------+-----------+-------------------------+
| 2014-07-15 19:33:11 | 1.760277  | DT46      | Production              |
+---------------------+-----------+-----------+-------------------------+
| 2014-07-15 21:18:48 | 0.637222  | DT46      | Unscheduled Maintenance |
+---------------------+-----------+-----------+-------------------------+
| 2014-07-15 21:57:02 | 3.109722  | DT46      | Production              |
+---------------------+-----------+-----------+-------------------------+
| 2014-07-16 01:14:15 | 4.128611  | DT46      | Production              |
+---------------------+-----------+-----------+-------------------------+
| 2014-07-16 18:33:01 | 0.004166  | DT46      | Unscheduled Maintenance |
+---------------------+-----------+-----------+-------------------------+
| 2014-07-16 19:19:38 | 2.580833  | DT46      | Production              |
+---------------------+-----------+-----------+-------------------------+
| 2014-07-17 01:23:56 | 0.111388  | DT46      | Unscheduled Maintenance |
+---------------------+-----------+-----------+-------------------------+
| 2014-07-17 01:30:37 | 0.293333  | DT46      | Production              |
+---------------------+-----------+-----------+-------------------------+
| 2014-07-17 01:48:13 | 0.99      | DT46      | Unscheduled Maintenance |
+---------------------+-----------+-----------+-------------------------+
| 2014-07-17 03:26:10 | 3.805833  | DT46      | Production              |
+---------------------+-----------+-----------+-------------------------+
| 2014-07-17 07:14:49 | 1.435833  | DT46      | Production              |
+---------------------+-----------+-----------+-------------------------+
| 2015-11-28 01:18:43 | 1.283611  | DT63      | Unscheduled Maintenance |
+---------------------+-----------+-----------+-------------------------+
| 2015-11-28 02:47:50 | 0.224166  | DT63      | Production              |
+---------------------+-----------+-----------+-------------------------+
| 2015-11-28 03:17:09 | 7.085277  | DT63      | Production              |
+---------------------+-----------+-----------+-------------------------+
| 2015-11-28 11:12:14 | 2.519722  | DT63      | Production              |
+---------------------+-----------+-----------+-------------------------+
| 2015-11-28 18:36:54 | 3.239166  | DT63      | Unscheduled Maintenance |
+---------------------+-----------+-----------+-------------------------+
| 2015-11-29 03:20:04 | 1.735833  | DT63      | Production              |
+---------------------+-----------+-----------+-------------------------+
| 2015-11-29 05:07:52 | 8.631944  | DT63      | Production              |
+---------------------+-----------+-----------+-------------------------+
| 2015-11-29 23:53:44 | 6.074444  | DT63      | Production              |
+---------------------+-----------+-----------+-------------------------+
| 2015-11-30 23:04:51 | 14.720555 | DT63      | Production              |
+---------------------+-----------+-----------+-------------------------+
| 2015-12-02 01:06:50 | 0.001111  | DT63      | Production              |
+---------------------+-----------+-----------+-------------------------+
| 2015-12-02 01:07:28 | 4.540277  | DT63      | Production              |
+---------------------+-----------+-----------+-------------------------+

Answer 1

select min(Moment), Equipment, sum(duration)
from (
  select *,
    case DowntimeType when 'Unscheduled Maintenance' 
      then row_number() over(partition by Equipment, DowntimeType order by Moment) 
      else row_number() over(partition by Equipment order by Moment) - row_number() over(partition by Equipment, DowntimeType order by Moment) end r
   from myTable
) t
where r > 0 -- must start with 'Unscheduled Maintenance' 
group by Equipment, r
order by Equipment, r

Answer 2

SELECT q1.moment, q1.equipment, sum(q.duration)+q1.duration
  FROM 
        (Select moment, equipment, duration, 
                rownumber() over partition (order by moment asc) rn
          from yourtable
         where downtimetype = 'Unschedule Maintenance') q1, 
         ( Select moment, 
            rownumber() over partition (order by moment asc) rn
              from yourtable
             where downtimetype = 'Unschedule Maintenance') as q2,
             yourtable q
where q2.rn =q1.rn+1
 AND q.moment > q1.moment
 AND q.moment < q2.moment
 AND q.downtimetype ='Production'
 AND q.equipment =q1.equipment
Group by q1.moment, q1.equipment

UNION ALL
-- This is because I couldnt get the last group
Select q1.maxmoment, q.equipment, 
        sum(q.duration)+q1.duration
  FROM (SELECT * from yourtable 
         where downtimetype = 'Unschedule Maintenance' 
           and moment = (SELECT max(moment) maxmoment
                           from yourtable 


    where downtimetype = 'Unschedule Maintenance' ) )q1,
            yourtable q
     WHERE q.downtimetype ='Production'
       AND q.equipment =q1.equipment
       AND q.moment > maxmoment
group by q.moment, q.equipment

1) "Unschedule Maintenance".

2) "" " ".

3) SUM (q1).

Answer 3

check this

select min(StartDate) 'Moment',min(Equipment) 'Equipment',sum(Duration) 'Total Duration' from mytable a
cross apply (select top 1 b.StartDate from mytable b where b.StartDate>a.StartDate and b.[DowntimeType]='Unscheduled Maintenance' order by StartDate asc) sq1(nextMaintenance)
group by nextMaintenance
order by min(StartDate)