Navigation

	Home Page
	Index
	All Pages

Quick Search
Advanced Search »

Contributor Links

	Create a new Page
	Administration
	File Management
	Login/Logout
	Your Profile

Other Wiki Sections

Software

Estimated Time of Completion Calculation - SQL Server

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