How to sum duration in a Gridview list using LINQ to SQL?
How are you doing today? I wish you all the best.
By the way, here's my problem. I would like to show the total number of sums in a gridview using LINQ to SQL. I have two tables: Users and Log_Times
Users
UserID ----- Name
1 ------------ Bob
2 ------------ Mary
3 ------------ Jane
Log_Times
ID --------------- User_ID -------------------- ------------ TimeIn --------- TimeOut
1 -------------------- 1 ----------------- ------ 1/1/2009 10:00:00 ------- 01/01/2009 11:05:00
2 ------------------ - 2 ----------------------- 1/1/2009 10:00:00 ------- 01.01.2009 11:02: 30
3 -------------------- ----------------------- 3 1/1 / 2009 10:00:00 ------- 01.01.2009 11:00:00
4 -------------------- 1 ------- ---------------- 1/2/2009 10:00:00 ------- 1/2/2009 11:05:00
5 -------------------- 2 ----------------------- 1/2/2009 10:00:00 ------- 1/2/2009 11:02:30
It would be so great if our gentle person can create a report that looks like this:
UserName ------- TotalDuration
Bob --------------- 2 hours, 10 minutes
Mary - ------------ 2 hours, 5 minutes
Jane -------------- 1 hour
I am using LINQ to SQL and VB.NET.
The code I have so far is:
Dim db = new LogTimeDataContext
Dim LogUser = from lu in db.Users select lu.Name, TotalDuration = ???
Gridview1.DataSource = LogUser
Gridview1.DataBind ()
Please help me figure out what is behind this TotalDuration code.
I am very grateful that you took your precious time to get through this issue and I am looking forward to a better answer to it. Thank you very much in advance.
a source to share
The solution that works fine is this.
Imports System.Data.Linq.SqlClient
Dim QueryLogUser = from u in db.Users
Select u.Name, Duration = u.Log_Times.Select (Function(lt) SqlMethods.DateDiffSecond(lt.TimeIn, lt.TimeOut))
Dim LogUser = From u In QueryLogUser.AsEnumerable _
Select u.Name, TotalDuration = u.Duration.Sum Order By TotalDuration Descending
This way I can get the second. Then I'll write another function in the gridview later to convert from Second to minutes and hour.
Dim totalSecond as Integer
Dim totalMinute as Integer
Dim totalHour as Integer
Protected Function FieldDisplayDuration(ByVal Second As Integer)
Dim d As String = "DefaultValue"
If (Second > 60)
totalSecond = Second mod 60
totalMinutes = Second / 60
If (totalMinutes > 60)
totalHour = totalMinute mod 60
totalMinute = totalMinute + totalMinute mod 60
return totalHour & totalMinute & totalSecond
End If
return totalMinute & totalSecond
End If
return totalSecond
End Function
This way I could get the duration column in Hour, Minute and Second.Happy format:
a source to share
You could write something like this
from t in db.Log_Times
join u in db.Users on t.UserID equals u.UserID
select new {user = u.name, Sum(t.TimeOut - t.TimeIn) AS Duration };
I don't know if the amount is correct, but it might be some route you want to go down.
I'm sure someone more experienced in SQL can modify the query correctly.
a source to share
Update:
Dim QueryLogUser = from u in db.Users
Select u.Name, Duration = u.Log_Times.Select (Function(lt) SqlMethods.DateDiffSecond(lt.TimeIn, lt.TimeOut)).Sum).OrderbyDescending(function(o) o.Duration)
The query below returns users who have an entry in the Log_times table.
Imports System.Data.Linq.SqlClient
Dim db = New LogTimeDataContext
Dim LogUser = From log In db.Log_Times _
Group By _
Name = log.User.Name _
Into _
Duration = Sum(SqlMethods.DateDiffMinute(log.TimeIn,log.TimeOut) _
Select name, TotalDuration = TimeSpan.FromMinutes(If(Duration,0)).tostring
The final line just checks that the duration is nothing before converting to a span of time
a source to share