473,395 Members | 1,639 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,395 software developers and data experts.

Elapsed time between 2 columns that are more than 24:00

I am trying to add time from several rows, and when they add up to more than 24 hours I do not get correct results I am looking for. I do understand that [time(0)] will not hold values larger than 24:00 Below is my script

Expand|Select|Wrap|Line Numbers
  1. SELECT
  2. Codes = (DeptCode + '-' + OpCode)
  3. ,TotalTime = convert(time(0),dateadd(second,sum(datediff(second,StartTime,FinishTime)),0))    
  4. ,Units = SUM(Units)
  5. ,UPH = cast(isnull(sum(Units) / nullif(sum(datediff(minute,StartTime,FinishTime))*1.0,0),0.0)*60  as decimal(10,0))
  6. ,'Goal%' = (convert(varchar,cast((isnull(sum(Units) / nullif(sum(datediff(minute,StartTime,FinishTime))*1.0,0),0.0)*60)/1552*100 as decimal(10,0))) + '%') 
  7. FROM PTW.dbo.TimeLog
  8. WHERE DeptCode = 'HS' and OpCode = 'HY'
  9. GROUP BY DeptCode,OpCode

Results are below

Expand|Select|Wrap|Line Numbers
  1. Codes   TotalTime   Units   UPH     Goal%
  2. HS-HY   14:07:00    69204   1114    72%
This can not be true because this is the breakdown.

Expand|Select|Wrap|Line Numbers
  1. ID#     Codes   TotalTime   Units   UPH     Goal%   AssociateName
  2. ---     -----   ---------   -----   ---     -----   -------------
  3. 2377    HS-HY   11:25:00    20891   1830    118%    NAME
  4. 3476    HS-HY   04:50:00    6978    1444    93%     NAME
  5. 43864   HS-HY   12:20:00    17628   1429    92%     NAME
  6. 2372127 HS-HY   03:20:00    4748    1424    92%     NAME
  7. 2372129 HS-HY   07:00:00    9158    1308    84%     NAME
  8. 2422946 HS-HY   00:47:00    949     1211    78%     NAME
  9. 21437   HS-HY   06:02:00    6530    1082    70%     NAME
  10. 2372090 HS-HY   11:00:00    2322    211     14%     NAME
  11. 63448   HS-HY   03:43:00    0       0       0%      NAME
  12. 2372061 HS-HY   01:40:00    0       0       0%      NAME
  13.  
When TotalTime yields more than 24:00 I am not getting correct result. Need help changing script to allow more than 24:
Can not figure a way around this......

BTW
My data types for StartTime and FinishTime are varchar(8)
Jul 7 '17 #1
0 1284

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: NotGiven | last post by:
Below is a good elapsed time function I found. However, I'd like to return total seconds instead of broken down into days, hours, minutes & seconds. In other words, I want "125" instead of "2...
4
by: Jim in Arizona | last post by:
I want to make buttons available or not available based on how much time has elapsed. An example: If NOW() 8 hours then btnOne.Visible = False Thanks. Jim
1
by: Bill | last post by:
I have a large number of records that have an elapsed time in seconds for each one that I was to have a total time spent. I can sum them up with query easily enough but I need to be able to display...
12
by: Spitfire | last post by:
I've a requirement to find the elapsed time between two function calls. I need to find the time elapsed accurate to 1 millisecond. The problem I'm facing right now is that, I'm using the 'time()'...
5
by: mmi48 | last post by:
Most of the discussions I've seen about elapsed time have, thus far, involved at least two date/time fields. I am trying to calculate time elapsed from one field across multiple records. The field...
12
by: pekka | last post by:
I'm trying to measure user input time with my Timer class object. It isn't as easy as I expected. When using std::cin between timer start and stop, I get zero elapsed time. For some unknown reason,...
0
by: Gabriel Genellina | last post by:
En Fri, 02 May 2008 16:13:41 -0300, Simon Pickles <sipickles@googlemail.comescribió: Two options: a) You can construct a datetime object with that info, using...
9
by: Ross | last post by:
I'm a newbie at this, and have searched a lot but can't find something that seems appropriate for measuring a recurring elapsed time. Creating an object with: var mydate = new Date(); seems...
1
by: karthick n | last post by:
Hi, I get the elapsed time from sys.dm_exec_query_stats table for each SQL query that I run in MSSQL. I see that the elapsed time for some of the SQL queries are shown as 0 while oracle...
5
by: Yammyrammy | last post by:
Hello, In my class I'm trying to display the elapsed time as my label on a windows form. I've been reading up on all the different articles online and have tried these three methods all with the...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.