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.
The problem really is that you're using variables, which limits you to working with one value at a time. For the same reason, it is advisable to avoid them when you need multiple data from a set. It is advisable to avoid the type
TIME
because it has a limit of 24 hours and then it restarts. It would be best to store the time in seconds or minutes depending on the precision you require.Either way, here is an option to fix it.
I use Datediff to get the seconds and use them to add. Then I divide by 3600 to get the hours and the remainder of the division with modulus (%) to get the minutes and seconds.