# 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 ServerMicrosoft SQL Server 2005

Last Comment
Kristie
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

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
Kevin Cross

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.
Kristie

Thanks! Solution works great.
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts

TRUSTED BY