What is the accurate way of calculating Average Time?

Question

What is the accurate way of calculating Average Time?

Dfalir

SSCrazy

Points: 2486
More actions
December 19, 2007 at 3:50 am

#75829

Guys, i need your info on the follwing issue please.
I have 5 entries for an employee of my company. Lets say these are the following
DAY_NAME DATE EMPLOYEE_ARRIVAL_TIME
Monday 03/12/20072007-12-03 09:36:23.667
Tuesday 04/12/20072007-12-04 10:56:08.440
Wednesday 05/12/20072007-12-05 09:55:23.233
Thursday 06/12/20072007-12-06 09:51:05.847
Friday 07/12/20072007-12-07 10:08:34.457
Now i want to calculate the average TIME that the employee arrives to the company. I have used the following approach. Is this the correct aproach?
Approach 1:
convert (datetime, avg (convert (float, (convert (datetime, [EMPLOYEE ARRIVAL TIME])))))
because when i use another approach i get different result. I do not know what the correct approach is...
Your suggestions would be appreciated pls...
"If you want to get to the top, prepare to kiss alot of bottom"

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 1

Use DATEDIFF to calculate the number of minutes after midnight the employee arrives each day, take an average of that, and then use DATEADD and DATEPART to manipulate that figure into a format you can use.

John

Dfalir SSCrazy Points: 2486 More actions · Answer 2

What you suggest is the second approach that i used and this approach produces different results than the first approach. You are right. My question is why is this the case, and what is the most accurate approach?

Can you clarify this issue for me? Why the first approach has different results? we know that every datetime is actually a float value. So why one is better than the other? any thoughts you may have, i am all ears.

"If you want to get to the top, prepare to kiss alot of bottom"

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 3

From Books Online: "Floating point data is approximate; therefore, not all values in the data type range can be represented exactly." Please post both queries you are using and the results you get and we'll try to work out whether the above is the reason for the discrepancy you are seeing.

John

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

Ummm... what's not in Books Online is the fact that the DATETIME datatype is a "fixed position float". I know... sounds contrary... what I mean is that DATETIME datatypes use a form of binary representation similar to float with a Mantissa and all that... only the exponent is fixed. Attempting to do any decimal math on times may lead to more inaccuracies than the float method.

Yes, I know... Books Online says the Date in an Integer and the Time is an integer... that's actually not correct... the DATETIME is a "fixed position float" at the "register" level.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Dfalir SSCrazy Points: 2486 More actions · Answer 5

So, thank you both for your concern these are my two approaches..

1) convert (datetime, avg (convert (float, (convert (datetime, [EMPLOYEE_ARRIVAL_TIME]))))) as AVG_ARRIVAL_TIME

2) DATEADD (MI, AVG (CONVERT (DEC(10,2), DATEDIFF(SS, REFERENCE_ARRIVAL_TIME, EMPLOYEE_ARRIVAL_TIME)/CONVERT (DEC (10,2), 60))), CONVERT (DATETIME, '09:30:00',103))

AS AVG_ARRIVAL_TIME

where reference arrival time is '1900-01-01 09:30:00.000' instead of 00:00

Now what do you thing is best of the two solutions and why!

Your comments wise guys! 🙂

"If you want to get to the top, prepare to kiss alot of bottom"

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

Your comments wise guys! 🙂

I know what your meant, but just so you know... the term "wise guy" in many English speaking countries is usually considered to be a fairly derogatory term... in other words, most would not take it as a compliment.

Second, for faster answers, consider the content of the following...

http://qa.sqlservercentral.com/articles/Best+Practices/61537/

To get the most accurate representation of the average time, this is how I'd do it...

--===== Create a test table (NOT PART OF THE SOLUTION)

CREATE TABLE #TimeKeeping (Day_Name VARCHAR(10), Date DATETIME, Employee_Arrival_Time DATETIME)

INSERT INTO #TimeKeeping

(DAY_NAME,DATE,EMPLOYEE_ARRIVAL_TIME)

SELECT 'Monday','03/12/2007','2007-12-03 09:36:23.667' UNION ALL

SELECT 'Tuesday','04/12/2007','2007-12-04 10:56:08.440' UNION ALL

SELECT 'Wednesday','05/12/2007','2007-12-05 09:55:23.233' UNION ALL

SELECT 'Thursday','06/12/2007','2007-12-06 09:51:05.847' UNION ALL

SELECT 'Friday','07/12/2007','2007-12-07 10:08:34.457'

--===== Solve the problem

SELECT CONVERT(CHAR(12),

CAST(

AVG(

CAST(Employee_Arrival_Time AS FLOAT)

- FLOOR(CAST(Employee_Arrival_Time AS FLOAT))

)

AS DATETIME)

,114)

FROM #TimeKeeping

Sure, sure... you could use some DATEDIFF and DATEADD functions to do all of this, but I've found the conversions to FLOAT to be just a tiny bit faster...

The real fact of the matter is that I think your table has a lot of unnecessary repeated information (I realize that the EmployeeID is probably missing from your example). For example, you can have a single DATETIME column for when the employee arrived and EVERYTHING else can be in calculated columns... makes the table a lot more efficient storage wise and also means you only have to do a single insert instead of all the gyrations you currently do...

DROP TABLE #TimeKeeping

GO

CREATE TABLE #TimeKeeping

(

EmployeeID INT,

Day_Name AS DATENAME(dw,Employee_Arrival_Time),

DateOnly AS DATEADD(dd,DATEDIFF(dd,'19000101',Employee_Arrival_Time),'19000101'),

Employee_Arrival_Time DATETIME,

FloatTimeOfDay AS CAST(Employee_Arrival_Time AS FLOAT) - FLOOR(CAST(Employee_Arrival_Time AS FLOAT)))

INSERT INTO #TimeKeeping

(EmployeeID,EMPLOYEE_ARRIVAL_TIME)

SELECT 1,'2007-12-03 09:36:23.667' UNION ALL

SELECT 1,'2007-12-04 10:56:08.440' UNION ALL

SELECT 1,'2007-12-05 09:55:23.233' UNION ALL

SELECT 1,'2007-12-06 09:51:05.847' UNION ALL

SELECT 1,'2007-12-07 10:08:34.457'

SELECT * FROM #TimeKeeping

... as you can see above, the inserts a simplified quite a bit. And, so are the averages...

SELECT EmployeeID,

CONVERT(CHAR(12),CAST(AVG(FloatTimeOfDay) AS DATETIME),114) AS AvgArrivalTime

FROM #TimeKeeping

GROUP BY EmployeeID

Not bad for a "wise guy", huh? 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Dfalir SSCrazy Points: 2486 More actions · Answer 7

Jeff,

needless to say I had no intention to offend neither you, nor John. 🙂

Thank you very much for your answer, and that is the reason that i keep coming back to SQL Server Central... Because Guys like you, actually deal with other people's question in such a professional way that has established SQLSerCen as one the best site for SQL Information.

I ll follow your suggestion,

Regards,

Dionisis

"If you want to get to the top, prepare to kiss alot of bottom"

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

Thanks for the high compliment, Dionisis :blush:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions