CODEWITHSUNDEEP
c#sql-serverlinq
akash
akash

Reputation: 173

How to get sum of hours and minutes in sql

SELECT LEFT(CONVERT(VARCHAR(10), OutTime - InTime, 108), 5) 
FROM Attendance 
WHERE InTime BETWEEN '01-01-2016' AND '01-31-2016' 
AND Employee=63 
ORDER BY InTime

I have got this result.

[table screenshot[1]

What I want is the sum of all the hours. And how can I write the same query in Linq?

Upvotes: 0

Views: 9954

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

SQL Server doesn't have a datetype that represents a time span. As such, it's usually best to convert everything into ints that represent the granularity of the result you need (here, minutes):

SELECT SUM(DATEDIFF(minute,InTime,OutTime)) as TotalMinutes
FROM Attendance 
WHERE InTime BETWEEN '20160101' AND '20160131' 
AND Employee=63 
--ORDER BY InTime

And then convert that into (days), hours and minutes in your presentation layer.

Also, as noted below the question, if you want to include events that occurred within the last day of January, change your WHERE clause:

SELECT SUM(DATEDIFF(minute,InTime,OutTime)) as TotalMinutes
FROM Attendance 
WHERE InTime >= '20160101' AND InTime < '20160201' 
AND Employee=63

Upvotes: 0

Max Sorin
Max Sorin

Reputation: 1052

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;
    }
}

Upvotes: 2

Related Questions