asked on 5/16/2011

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

Microsoft SQL Server Microsoft SQL Server 2005

Last Comment

Kristie

5/16/2011

Kevin Cross

5/16/2011

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

5/16/2011

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

5/16/2011

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Kristie

5/16/2011

ASKER

Thanks! Solution works great.