Calculate running hours

Kristie
Kristie used Ask the Experts™
on
I have a table with the below column and will declare starttime for the 1st line item.  From there, I'd like to calculate running hours.  For example:  counter 1 will start at 6:00am.  The job will take 122.25 hours.  Calculate for counter 1, 6:00am * 122.25 hours.  For counter 2, end result of counter 1 + hours, etc.

counter      hours      days
1      122.2571429      5.094047619
2      122.2571429      5.094047619
3      122.2571429      5.094047619
4      31.42857143      1.30952381
5      122.2571429      5.094047619
6      122.2571429      5.094047619
7      122.2571429      5.094047619
8      31.42857143      1.30952381
9      122.2571429      5.094047619
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
What version of SQL are you using? The zones show SQL 2005, just confirming that it is okay to use features introduced in that version, i.e., not worry about backward compatibility.

If needed, is multi-line SQL okay or are you looking for a single select?

What do you expect the final results to look like? i.e., is this a date and time or simply the time of day of whatever amount of days and hours is a result of adding 122 hours to 6:00 a.m. is like 8:15 a.m.

How many rows total are we talking about? Some solutions like recursion might have a limit.
Kristie

Author

Commented:
version:  Microsoft SQL Server 2005 - 9.00.3068.00 (X64)   Feb 26 2008 23:02:54   Copyright (c) 1988-2005 Microsoft Corporation  Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

multi-line is fine.

Final Result expected:
counter           hours              days              end date/time        
1      122.2571429      5.094047619      5/17/11 8:00 AM
2      122.2571429      5.094047619      5/22/11 8:00 AM
3      122.2571429      5.094047619      5/27/11 8:00 AM
4      31.42857143      1.30952381      5/28/11 1:00 PM

total rows are dynamic
Chief Technology Officer
Most Valuable Expert 2011
Commented:
Okay, try something like this (may not be greatest performance due to the subqueries, but it avoids use of recursion which may have limits based on the size of your dataset):

select [counter], [hours], [days]
     , dateadd(HH,
               (select sum([hours]) from your_table b where b.counter <= a.counter),
               '2011-05-12 06:00:00') [end date/time]
from your_table a;

Open in new window


If you want this accurate to the minute, then you could do this:
select [counter], [hours], [days]
     , dateadd(MI,
               (select sum([hours]*60) from your_table b where b.counter <= a.counter),
               '2011-05-12 06:00:00') [end date/time]
from your_table a;

Open in new window


And so on for seconds; however, if you wanted it as precise as possible then note with datetime being in FLOAT number of days, that you could simply add your days calculation.

select [counter], [hours], [days]
     , convert(datetime, '2011-05-12 06:00:00')
	   + (select sum([days]) from your_table b where b.counter <= a.counter) [end date/time]
from your_table a;

Open in new window


Hope that helps!
Kristie

Author

Commented:
Thanks! Solution works great.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial