Adding nvarchar values as Time using SUM function in SQL Server

Question

I am still a rookie in SQL Server so I can't quite figure this one out.

I have a column that is filled with nvarchar values that I need to sum as durationOfWork, so it needs to do the sum function for time values.

The columns are like this:

DurationOfWork  FirstName
--------------------------
1h:30min        Peter
2h:0min         Sandra
0h:10min        Peter
5h:32min        Peter
2h:0min         Dave
1h:43min        Dave
0h:25min        Sandra

I need to return for each Person the sum of their durations of work.

SUM(DurationOfWork)  FirstName
------------------------------
7h:12min             Peter
2h:25min             Sandra
3h:43min             Dave

Any help would be great.

Answer 1

What a pain. I would recommend calculating the duration as minutes or decimal hours, instead of converting back to a string. The idea is to extract what you want from the string and put it in a better format:

select firstname,
       sum( convert(int, left(durationofwork, 1)) * 60 +
            convert(int, substring(durationofwork, 4, 2))
          ) as duration_minutes
from t
group by firstname;

If your values are always less than 24 hours -- which they seem to be based on the format -- then I would suggest storing them as time values rather than strings. That said, sum()s might exceed 24 hours, so you can't use times for aggregated results.

EDIT:

To handle the variable minutes:

select firstname,
       sum( convert(int, left(durationofwork, 1)) * 60 +
            convert(int, replace(substring(durationofwork, 4, 2), 'm', ''))
          ) as duration_minutes
from t
group by firstname;