Calculate average time differences

Question

I have a table with different IDs and for each ID there are two different times (Time1 and Time2). I need to check if Time1 is bigger than Time2 then I need to subtract X= Time1 -Time2. If Time2 is bigger I need to subtract Y=Time2 - Time1.

This is my result of the query:

ID      Time1                   Time2              Result
1   2018-02-12 08:00:00   2018-02-12 09:28:00     -00:32
2   2018-02-12 07:00:00   2018-02-12 08:04:00     -00:56
3   2018-02-12 06:00:00   2018-02-12 08:10:00      00:10
4   2018-02-12 06:00:00   2018-02-12 08:34:00      00:34

If Time1 is bigger I add a '-' in the output to differentiate. What I want now is to calculate the average of the result times but separately for the positive ones and negatives one. What I want is something like this:

(00:32 + 00:56) /2

and

(00:10 + 00:34) /2

Time1 and Time2 are stored as Datetimes but I convert them to strings because I just want to see the time difference I don't need dates. Is there a way to calculate the average?

Answer 1

use avg() to find the average. To find the average of negatives -

SELECT AVG(Result) FROM table_name WHERE Result like '-%';

Answer 2

separate:

select time1, time2, result ,
case when result like '-%' then avg(result)
case when result like '+%' then avg(result)
else result
end new_result
from table

If you DO not want to segregate positive and negative:

select avg(total) from
( select sum(ABS(result_col) as total from table) x

Answer 3

I had to exclude the logic for getting the column Result here, as your logic doesn't make sense (as described in my comment). To get the average, however, you have to do a lot of conversion. Negative Time doesn't exist (in SQL Server), so you have to do string manipulations and conversions to get the values you want.

If you need to work out Result in this query too, then you need to explain it better. '09:28' - '08:00' is not "'-00:32'" (and certainly not a negative, as the first value is great). It makes no sense.

Anyway, assuming you do have the value/logic for Result already:

CREATE TABLE #Time (ID int,
                    Time1 datetime2(0),
                    Time2 datetime2(0),
                    Result varchar(6))
INSERT INTO #Time
VALUES (1,'20180212 08:00:00','20180212 09:28:00', '-00:32'),
       (2,'20180212 07:00:00','20180212 08:04:00', '-00:56'),
       (3,'20180212 06:00:00','20180212 08:10:00', '00:10'),
       (4,'20180212 06:00:00','20180212 08:34:00', '00:34');
GO

WITH Conversion AS (
    SELECT CASE LEFT(Result,1) WHEN '-' THEN CONVERT(time, STUFF(Result,1,1,''))
                               ELSE CONVERT(time, Result)
           END AS AbsoluteResult,
           CASE LEFT(Result,1) WHEN '-' THEN '-' ELSE '' END AS PosNeg
    FROM #Time T)
SELECT PosNeg + LEFT(CONVERT(time,DATEADD(SECOND, AVG(DATEDIFF(SECOND, 0, AbsoluteResult)), 0)),5) AS AverageTime
FROM Conversion
GROUP BY PosNeg;

GO
DROP TABLE #Time;