Determine how long a phase takes, looking at current row and next row

Question

I am working on a project for work, data logging type system. Currently using SQL Server 2008, can upgrade if needed. Currently we are taking 1 minute intervals of the equipment, we are gathering all sorts of information, I am only worried about Time, and Step Number.

I am trying determine the length of time, the machine spends in a certain step before moving out. I have a way I am using that it currently works. However it takes to long to run. (5+ Hours)

I have a view set up currently that looks like this.

With Steps AS (SELECT        *, ROW_NUMBER() OVER (ORDER BY Time) AS RowNum 
FROM dbo.Data_All)

SELECT        Steps.Time, Steps.[Production Step]
FROM            Steps INNER JOIN
Steps AS Y ON Steps.RowNum = y.RowNum + 1 AND 
Steps.[Production Step] <> y.[Production Step]

Doing that gives me a breakdown, with time that it entered that step. This will take about 30 seconds to run, so not to bad. Realize we have over 250,000 records its looking through, and growing each day.

| Time            | Step #  | 
-----------------------------
| 7-25-2014 14:32 | 11      |
| 7-25-2014 15:32 | 13      | 
| 7-25-2014 15:40 | 14      | 
| 7-25-2014 15:42 | 15      | 
| 7-25-2014 15:50 | 8       |

I am then running a query based off that view, to get me, Step, Time, Next Step Time, and Duration. This is query is what is taking to long. So I am looking for a way to do this faster.

With CTE AS (select Time, Row_Numbers=ROW_NUMBER() Over (Order by Time), 
[production step]  from Time_In_Step) 

  Select [Current Row].[Production Step], [current row].Time, 
[next row].Time AS [Next Step Time], Datediff(mi, [current row].Time,
[Next row].time) AS [Duration in Min]
  FROM CTE [Current Row]
  LEFT JOIN CTE [Next Row] ON 
  [Next Row].Row_Numbers = [Current Row].Row_Numbers + 1

Doing this I get the following, which is what I want. Just a faster way to do it.

| Time            | Step #  | Next Step Time  | Duration in Min |
-----------------------------------------------------------------
| 7-25-2014 14:32 | 11      | 7-25-2014 15:32 | 60              |
| 7-25-2014 15:32 | 13      | 7-25-2014 15:40 | 8               |
| 7-25-2014 15:40 | 14      | 7-25-2014 15:42 | 2               |
| 7-25-2014 15:42 | 15      | 7-25-2014 15:50 | 8               |
| 7-25-2014 15:50 | 8       | Etc.....        | DateDiff

Anyone ideas on how to optimize this? Please let me know if you need any more ideas about what I am trying to do.

This is a variation n the Running-Total problem in SQL. You can obtain other resources by Googling that phrase. — Pieter Geerkens, Jul 27 '14 at 20:28
Thanks, I will read into that and see if it can help me solve anything more. — user2644176, Jul 27 '14 at 21:59

Gordon Linoff · Accepted Answer · 2014-07-27T20:34:38.743

1

I would suggest using cross apply:

select c.[Production Step], c.Time, 
       n.Time AS [n Step Time],
       Datediff(mi, c.Time, n.time) AS [Duration in Min]
from time_in_step c cross apply
     (select top 1 n.*
      from time_in_step n
      where c.time < n.time
     ) n;

An index on time would help the performance.

edited Jul 27 '14 at 20:34

answered Jul 27 '14 at 20:17

Gordon Linoff

1,242,037
58
646
786

Thank you for the quick reply. It seems to be working. Just one questions, in the subquery, did you mean to have "curr.time" or is that supposed to be c.time to match what you called it up above? Either way it seems to be working, and I appreciate the quick reply. – user2644176 Jul 27 '14 at 20:29
@user2644176 . . . I meant `c`. I put in `curr` and `next` originally and then realized that these are reserved words. – Gordon Linoff Jul 27 '14 at 20:34

score 0 · Answer 2 · answered Jul 27 '14 at 20:24

0

You could try the following:

select c.production_step,
       c.time,
       case when f.time = c.time then null else f.time end as next_step_time,
       datediff(mi, c.time, f.time) as dur_in_mins
  from time_in_step c
 cross join time_in_step f
 where f.time = (select min(x.time) from time_in_step x where x.time > c.time)
    or (not exists (select 1 from time_in_step x where x.time > c.time)
   and f.time = c.time)

answered Jul 27 '14 at 20:24

Brian DeMilia

13,103
1
23
33

Perfect, it is running currently. I will see how well it works. I kind of feel like a fool seeing how fast everyone answered this. – user2644176 Jul 27 '14 at 20:51
@user2644176 np, it might not run as fast as gordon's query but can you try it out. in sql server 2012 you could more easily use the LAG function to get the previous time value, but not in sql server 08 – Brian DeMilia Jul 27 '14 at 20:54

Determine how long a phase takes, looking at current row and next row

2 Answers2