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

Page History: Estimated Time of Completion Calculation - SQL Server

Compare Page Revisions



« 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

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