Addition of Total Hours in SQL Server

How to get sum of hours and minutes in sql

CREATE TABLE #Attendance(OutTime DateTime, InTime DateTime)

-- Sample Data
insert into #Attendance( InTime, outTime)
values ('2015-12-12 07:30', '2015-12-12 17:30'),
('2015-12-12 07:30', '2015-12-12 17:30'),
('2015-12-13 07:30', '2015-12-13 16:45'),
('2015-12-14 07:30', '2015-12-14 14:34'),
('2015-12-15 07:30', '2015-12-15 18:21')

-- Use DateDiff function to get total minutes between times to achieve greater accuracy then with 'hour'
-- Convert total minutes to hours after adding up total minutes
SELECT SUM(DATEDIFF(MINUTE, InTime , OutTime)) /60
FROM #Attendance

DROP TABLE #Attendance

Now with more LINQ:

public class Attendance
{
public DateTime InTime { get; set; }
public DateTime OutTime { get; set; }
}

[TestClass]
public class AttendanceUser
{
[TestMethod]
public void UseALambda()
{
var rand = new Random();
var attendances = Enumerable.Range(0, 10).Select(x =>
new Attendance { InTime = DateTime.Now.AddHours(-rand.Next(x)), OutTime = DateTime.Now.AddHours(rand.Next(x)) }).ToList();

var total = attendances.Sum(x => (x.OutTime - x.InTime).TotalMinutes) / 60;
}
}

SQL - Calculate Hours sum using rollup

I did have similar requirement and solved it like this:

select type, RTRIM(time/60) + ':' + RIGHT('0' + RTRIM(time%60),2) from
(
select type= ISNULL(type, 'Total'),
time= SUM(DATEDIFF(MINUTE, '0:00:00', time))
from time_test
GROUP BY ROLLUP(type)
) x

I believe this is more understandable and easier to trace

type    time
A 1:25
B 2:30
C 5:56
D 0:50
Total 10:41

Edit: Updated the query for hour can be more then 24 hours

select type, RTRIM(time/60) + ':' + RIGHT('0' + RTRIM(time%60),2) from
(
select type= ISNULL(type, 'Total'),
time= SUM(DATEDIFF(MINUTE, '0:00:00',
DATEADD(day, SUBSTRING(time,0,CHARINDEX(':',time,0)) / 24,
DATEADD(hour, SUBSTRING(time,0,CHARINDEX(':',time,0)) % 24,
DATEADD(minute,SUBSTRING(time,CHARINDEX(':',time)+1,LEN(time)) +0, 0)) )))
from time_test
GROUP BY ROLLUP(type)
) x

Example Result:

A       1:25
B 2:30
C 5:56
D 70:50
Total 80:41

How to get the sum of hour, minutes, and seconds in 2 columns?

As mentioned in comments - if you're storing time values, you should really be using the TIME datatype - not a VARCHAR ..... always use the most appropriate datatype - no exceptions.

Anyway - since you have VARCHAR, the statement get a bit convoluted - but here you go:

SELECT 
DATEADD(SECOND, DATEDIFF(SECOND, 0, CAST(NewTime AS TIME)), CAST(OldTime AS TIME))
FROM
dbo.YourTableNameHere

which would look a lot easier with TIME columns, of course:

SELECT 
DATEADD(SECOND, DATEDIFF(SECOND, 0, NewTime), OldTime)
FROM
dbo.YourTableNameHere


Related Topics



Leave a reply



Submit