How to add total attendance hours as in the picture
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
in my case i need to update Logout Date Time (as an input and cast with login date time in the same date)
and then update total attendance(with login date time and logout date time as input)
hi Andrew,
Didn't exactly get as to what you want to do. From the attached file, you are trying to update the LogoutDateTime and TotalAttendanceofHours based on login time being same as logout time and the TotalAttendanceofHours in secs? But how can login time be same as logout time?
Regards,
Shilpa