Avatar of Kristie
Kristie

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

Avatar of undefined
Last Comment
Kristie
Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image

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.
Avatar of Kristie
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
Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image

Blurred text
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
Avatar of Kristie
Kristie

ASKER

Thanks! Solution works great.
Microsoft SQL Server
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
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo