Page History: Estimated Time of Completion Calculation - SQL Server

Compare Page Revisions

0 Current 0

« Older Revision - Back to Page History - Current Revision

Page Revision: Wed, May 20, 2009, 1:18 PM

The following SQL will calculate the estimated time of completion for a processing job, given the job's start time, the total number of rows to be processed, and the number of rows processed so far.

declare @StartTime datetime
declare @TotalRows int
declare @RowsDone int


select 
     @StartTime       = '05/20/2009 01:00:00am'
    ,@TotalRows       = 24
    ,@RowsDone        = 12

with MyData as (
    select 
         Ctime      = getdate()
        ,SecElap    = convert(float, datediff(s, @StartTime, getdate()))
        ,RowsDone   = convert(float, @RowsDone)
        ,RowsLeft   = convert(float, @TotalRows - @RowsDone)
    )
select
     CTime
    ,RowsDone
    ,RowsLeft 
    ,Etc = convert(varchar(30), dateadd(s, SecElap * RowsLeft / RowsDone, CTime), 0)
from 
    MyData