Transact-SQL to sum up elapsed time

Question

I have a table in my database the records start and stop times for a specific task. Here is a sample of the data:

Start                       Stop
9/15/2008 5:59:46 PM        9/15/2008 6:26:28 PM
9/15/2008 6:30:45 PM        9/15/2008 6:40:49 PM
9/16/2008 8:30:45 PM        9/15/2008 9:20:29 PM
9/16/2008 12:30:45 PM       12/31/9999 12:00:00 AM

I would like to write a script that totals up the elapsed minutes for these time frames, and wherever there is a 12/31/9999 date, I want it to use the current date and time, as this is still in progress.

How would I do this using Transact-SQL?

Answer 1

SELECT  SUM( CASE  WHEN Stop = '31 dec 9999' 
                   THEN DateDiff(mi, Start, Stop)
                   ELSE DateDiff(mi, Start, GetDate())
             END ) AS TotalMinutes 
FROM    task

However, a better solution would be to make the Stop field nullable, and make it null when the task is still running. That way, you could do this:

SELECT  SUM( DateDiff( mi, Start, IsNull(Stop, GetDate() ) ) AS TotalMinutes 
FROM    task

Answer 2

I think this is cleaner:

   SELECT  SUM(
               DATEDIFF(mi, Start, ISNULL(NULLIF(Stop,'99991231'), GetDate()))
              ) AS ElapsedTime
   FROM Table