16
Views
3
Comments
Solved
sql

How to add total attendance hours as in the picture

sql.PNG

Rank: #70
Solution

Hi Andrew,


You want to update the field TotalAttendanceOfHours with the number of hours between logout (as an input) and login for the records with no logout date time, is that it?

If that is the case, consider the following SQL:


Update {Attendance}

Set {Attendance}.[LogoutDateTime] = @logoutDateTime,
{Attendance}.[TotalAttendanceOfHours] = DATEDIFF(hh, {Attendance}.[LoginDateTime], @logoutDateTime)

Where {Attendance}.[UserId]=@UserId
/* if you want to update only the login record with no logout date */
and {Attendance}.[LogoutDateTime] = @NullDate


If you want to update only the login record with no logout date, add the last AND clause and the parameter NullDate with the NullDate() value.

If you want to fill the difference from hours to seconds, for example, you can just change the hh to ss (more information on DATEDIFF datepart parameters here) but I would advise changing the number of the attribute TotalAttendanceOfHours to TotalAttendanceOfSeconds to avoid confusion.

Hope it helps.


Regards,
João