asked on

Calculate running hours

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

Kevin Cross

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

ASKER

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

ASKER CERTIFIED SOLUTION

Kevin Cross

membership

This solution is only available to members.

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

Start Free Trial

Kristie

ASKER

Thanks! Solution works great.