Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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

a
03:59:00.0000000
05:13:00.0000000

Expected Output

AvgTime
04:36:00.0000000

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

Advertisements