Jasinski Technical Wiki

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

PoweredBy

Estimated Time of Completion Calculation - SQL Server

RSS
Modified on Sun, Mar 18, 2012, 12:55 PM by Administrator Categorized as Algorithms, 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

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2018, Patrick Jasinski.