I am trying to add the time of each user but I can only add the total time of all. I was thinking that with a GROUP BY but I can't add them.
My Query is the following:
DECLARE @TalkTime TABLE (
payroll_id VARCHAR(255),
talkTime TIME(4)
)
INSERT INTO @TalkTime
([payroll_id],[talkTime])
VALUES
('1','0:45:00'),
('2','4:19:00'),
('1','4:21:00'),
('2','5:52:00'),
('1','4:59:00'),
('2','6:06:00'),
('1','5:03:00'),
('2','5:51:00'),
('1','5:18:00')
;
DECLARE @hours INT
DECLARE @mins INT
DECLARE @secs INT
SELECT @hours = SUM(DATEDIFF(HOUR, '0:00:00', talktime)) +
((SUM(DATEDIFF(minute, '0:00:00', talktime)) - (SUM(DATEDIFF(hour, '0:00:00', talktime)) *60)) / 60)
FROM @TalkTime
SELECT @mins = (SUM(DATEDIFF(SECOND, '0:00:00', talktime)) - (@hours*60*60))/60
FROM @TalkTime
SELECT @secs = (SUM(DATEDIFF(SECOND, '0:00:00', talktime))) - ((@hours*60*60) + (@mins*60))
FROM @TalkTime
SELECT CAST(@hours AS NVARCHAR(4)) + ':' + CAST(@mins AS NVARCHAR(2)) + ':' + CAST(@secs AS NVARCHAR(2)) AS TOTAL_TIME
The result I get is the following:
TOTAL_TIME |
---|
42:34:0 |
And the result I expect to get is the following (it's an example):
payroll_id | total_time |
---|---|
1 | 01:40 |
two | 05:20 |
I suppose that with a GROUP BY
se it should be able but I have no idea how to achieve it.