SQL -- Derive Date Difference Column

Question

 
+------+-------------------------+
| proc |                 endTime |
+------+-------------------------+
|    A | 2010/01/01 12:10:00.000 |
|    B | 2010/01/01 12:08:00.000 |
|    C | 2010/01/01 12:05:00.000 |
|    D | 2010/01/01 12:02:00.000 |
|   ...|                     ... |

So basically the data I pull from SQL will look something like the above, with the first column being the name of a process, and the second column the time it finished running. I want to add a THIRD column, where it displays the running time of the process.

Basically, I want the data pulled to look like this instead:

 
+------+-------------------------+--------------+
| proc |                 endTime |  runningTime |
+------+-------------------------+--------------+
|    A | 2010/01/01 12:10:00.000 |              | (process a is not done running)
|    B | 2010/01/01 12:08:00.000 | 00:03:00.000 |
|    C | 2010/01/01 12:05:00.000 | 00:03:00.000 |
|    D | 2010/01/01 12:02:00.000 | 00:02:00.000 | (assume 12:00 start time)
|   ...|                     ... |          ... |

And I know it would be easier it add a "startTime" column and from that determine runningTime, but I don't have access to change that, and regardless the old data would not have a startTime to work with anyways.

The first process's start time is arbitrary, but you see what I'm getting at. We know the run time of proc C based on when proc D ended, and the when proc C ended (subtract the first from the second).

How do I compute that third row based on the difference between "Row X Col B" and "Row X-1 Col B"?

Thanks.

Answer 1

I don't think you can add it as a "calculated column". You can calculate it in a view pretty easily like this (all code for MSSQL. Your convert function may vary):

select 
    e1.RowID, 
    e2.EndTime as StartTime, 
    e1.EndTime, runningtime=convert(varchar(20), e1.EndTime - e2.EndTime, 114)
from endtimetest e1
    left join endtimetest e2 on e2.endtime = 
            (Select max(endtime) 
              from endtimetest 
              where endtime < e1.Endtime) 

Or, you could calculate it in a trigger with something similar.