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.
Advertisement
Answer
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 time
s 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;