asked on

Predicting a load finish time in SQL

I am trying to derive/predict a time/timestamp in SQL when our daily load might complete. Can someone help with this?

I have a datetime field from a table [Start].
I then have a dynamically generated [Cycle_Dur_AVG column which show the average time based on the last rolling three months,

CONVERT(VARCHAR(12), AVG(DATEDIFF_BIG(SECOND, [DDS_Dim_Start], [End])) / 60 / 60 / 24) + 'd :' + 
      CONVERT(VARCHAR(12), AVG(DATEDIFF_BIG(SECOND, [DDS_Dim_Start], [End])) / 60 / 60 % 24) + 'h :' + 
      CONVERT(VARCHAR(2), AVG(DATEDIFF_BIG(SECOND, [DDS_Dim_Start], [End])) / 60 % 60) + 'm :' + 
      CONVERT(VARCHAR(2), AVG(DATEDIFF_BIG(SECOND, [DDS_Dim_Start], [End])) % 60) + 's' AS [CYCLE_DUR_AVG]

Open in new window

which outputs something like '0 d 12 h 23 m 5 s'.

Is there a way I can add those average seconds to the Start column and output a timestamp/datetime field?

Thanks,

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Erik Hauser

ASKER

Thank you Scott. I was close to this before I posted but I think I had the wrong order. Thank you sir. Now I just need to see if I can get it to calculate a time while a load is running, ie [End] is null.

Erik Hauser

ASKER

Ahh I see what's happening. Its taking the average for each row instead of over all. I basically need to create a variable that calculates the seconds overall and then plugs that number into your code piece.

Erik Hauser

ASKER

Thank you sir! You code worked wonderfully and because of your code, you allowed me to think through the process. Wonderful, thank you!