SQL Puzzle | Average Time Puzzle

In this puzzle you have to find the average a column, this column contains time data. Please check out the sample input values and sample expected output below.

Sample Input

Expected Output

Script

Use below script to create table and insert sample data into it.

--

SELECT a  FROM
(
	SELECT CAST('03:59:00.0000' AS TIME) a UNION ALL
	SELECT CAST('05:13:00.0000' AS TIME) a
)i

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution – 1

--


SELECT cast(cast(avg(cast(CAST(a as datetime) as float)) as datetime) as time) AvgTime FROM
(
	SELECT CAST('03:59:00.0000' AS TIME) a UNION ALL
	SELECT CAST('05:13:00.0000' AS TIME) a
)i

--

Output

--

/*------------------------
SELECT cast(cast(avg(cast(CAST(a as datetime) as float)) as datetime) as time) AvgTime FROM
(
	SELECT CAST('03:59:00.0000' AS TIME) a UNION ALL
	SELECT CAST('05:13:00.0000' AS TIME) a
)i
------------------------*/
AvgTime
----------------
04:36:00.0000000

(1 row(s) affected)


--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com