SQL Query with computed column

Question

help me please with a query. Assume that we have a table with columns:

• Transaction
• StartTime
• EndTime

Now, I need a query with computed column of (value = EndTime-Startime). Actually I need to group Users(Transaction has a FK for Users) and sort them by average time spent for transaction.

Answer 1

You should look into the DateDiff function. You did not specify the units of the timespan, so I assumed seconds in my solution:

Select ...
From dbo.Users As U
    Join    (
            Select T.UserId, Avg( DateDiff(s, T.StartTime, T.EndTime) ) As AvgTimespan
            From dbo.Transactions As T
            Group By T.UserId
            ) As Z
        On Z.UserId = U.Id
Order By Z.Timespan Desc

Answer 2

SELECT u.userid
       ,AVG(DATEDIFF(second, StartTime, EndTime) AS AvgTime
FROM trxn AS t
INNER JOIN users AS u
    ON trxn.userid = u.userid
GROUP BY u.userid
ORDER BY AVG(DATEDIFF(second, StartTime, EndTime)