Ruhaan Ruhaan - 4 months ago 18
SQL Question

Find Monthly Average of remaining time

I am using SQL Server 2008R2, I have a Table named

Timings
which has Column names
StartTime
and
EndTime
.
What I need is to find the difference between
StartTime
and
EndTime
in time only
(HH:MM)
,not the date, and the result which I get after the difference, I need monthly
Average
of that difference.
My Columns are in
DateTime
datatype which are as follows,

---------------------------------------
STARTTIME ENDTIME
---------------------------------------
02/08/2016 14:29 02/08/2016 14:30
07/07/2016 15:37 07/07/2016 15:37
07/07/2016 15:38 07/07/2016 15:39
07/07/2016 15:39 07/07/2016 15:39
07/07/2016 15:40 07/07/2016 15:40
07/07/2016 15:44 07/07/2016 15:45
07/07/2016 15:45 07/07/2016 15:45
07/07/2016 15:46 07/07/2016 15:46
07/07/2016 15:46 07/07/2016 15:53
07/07/2016 15:53 07/07/2016 15:54
07/07/2016 15:54 07/07/2016 15:54
02/12/2016 9:28 02/12/2016 9:28
02/12/2016 9:29 02/12/2016 9:29
02/12/2016 9:30 02/12/2016 9:30
02/12/2016 9:33 02/12/2016 9:34
02/12/2016 9:34 02/12/2016 9:35


So far I have tried this,

;With CTTT
AS (
Select STARTTIME
,ENDTIME
,(ENDTIME-STARTTIME) AS R
From Timings
)
Select AVG(Convert(Varchar(10),R,108))
From CTTT


I am getting this Error message,


Msg 8117, Level 16, State 1, Line 6
Operand data type varchar is invalid for avg operator.


I am not good at SQL,A help would be much appreciable.

Answer

Here's a working script that converts average datetime difference to hh:mm format.

--DROP TABLE #Test
--DROP TABLE #TestTemp

CREATE TABLE #Test
(
    [START]         DATETIME NOT NULL,
    [END]           DATETIME NOT NULL,
);

INSERT INTO #Test
SELECT
    '02/08/2016 14:29', '02/08/2016 14:34'
UNION
SELECT
    '02/08/2016 14:39', '02/08/2016 14:47'

SELECT * FROM #Test

SELECT 
MONTH([START]) AS 'MONTH', 
YEAR([START]) AS 'YEAR', 
SUBSTRING(CONVERT(VARCHAR, CAST(AVG(CONVERT(FLOAT, [END] - [START])) AS DATETIME),108),1,5) AS 'DIFFERENCE' 
FROM #Test
GROUP BY 
MONTH([START]),
YEAR([START])

OUTPUT:

START   END
2016-02-08 14:29:00.000 2016-02-08 14:34:00.000
2016-02-08 14:39:00.000 2016-02-08 14:47:00.000

MONTH   YEAR    DIFFERENCE
2       2016    00:06

For your convenience:

SELECT 
MONTH(STARTTIME) AS 'MONTH', 
YEAR(STARTTIME) AS 'YEAR', 
SUBSTRING(CONVERT(VARCHAR, CAST(AVG(CONVERT(FLOAT, ENDTIME-STARTTIME)) AS DATETIME),108),1,5) AS 'DIFFERENCE' 
FROM 
Timings
GROUP BY 
MONTH(STARTTIME),
YEAR(STARTTIME)