I have prepared an attendance system where an employee can put his IN TIME in the morning and can put OUT TIME in the evening.
Logic 1 : Without putting IN TIME he can not put OUT TIME and once he had done with IN-OUT TIMES he can not repeat it for the same day.
It is working perfectly without issue.
Now a new requirement came that there are also some workers (security guards) who need to take attendance at night. Lets say a security guard comes at evening 19:00 and go 04:00 in the morning.
How to put the same Logic 1 for security guards too. (SQL SQL level)
Currently, I am doing like below.
IN TIME
BEGIN
SET @In_Date_Time=DATEADD(MI, 330, SYSUTCDATETIME())
IF NOT Exists (SELECT user_id,Name,In_Date_Time from tbl_Attendance where user_id=@user_id AND cast(In_Date_Time AS DATE)=cast(@In_Date_Time AS DATE) )
BEGIN
INSERT INTO tbl_Attendance (user_id,In_Date_Time,Name,Location,in_picture,attendance_type,location_name,tbt_talk,working_note)
VALUES (@user_id,@In_Date_Time,@Name,@Location,@in_picture,@attendance_type,@location_name,@tbt_talk,@working_note)
END
OUT TIME
BEGIN
SET @In_Date_Time=DATEADD(MI, 330, SYSUTCDATETIME())
UPDATE tbl_Attendance
SET out_date_time=@out_date_time, out_picture=@out_picture, out_working_note=@out_working_note,out_location=@out_location
WHERE user_id=@user_id AND cast(In_Date_Time AS DATE)=cast(@In_Date_Time AS DATE)
END
checking if already have IN TIME
SET @In_Date_Time=DATEADD(MI, 330, SYSUTCDATETIME())
IF NOT Exists (SELECT user_id from tbl_Attendance where user_id=@user_id AND cast(In_Date_Time AS DATE)=cast(@In_Date_Time AS DATE) )
BEGIN
SELECT '0'
END
ELSE
BEGIN
SELECT '1'
END
Checking if already done OUT TIME
SET @In_Date_Time=DATEADD(MI, 330, SYSUTCDATETIME())
IF NOT Exists (SELECT user_id from tbl_Attendance where user_id=@user_id AND cast(In_Date_Time AS DATE)=cast(@In_Date_Time AS DATE) AND out_date_time<>'')
BEGIN
SELECT '0'
END
ELSE
BEGIN
SELECT '1'
END
Please suggest
It is our choices, that show what we truly are, far more than our abilities.
Participant
1448 Points
2845 Posts
Manage attendance for night shifts : at sql level
May 19, 2020 06:16 PM|demoninside9|LINK
Hi All,
I have prepared an attendance system where an employee can put his IN TIME in the morning and can put OUT TIME in the evening.
Logic 1 : Without putting IN TIME he can not put OUT TIME and once he had done with IN-OUT TIMES he can not repeat it for the same day.
It is working perfectly without issue.
Now a new requirement came that there are also some workers (security guards) who need to take attendance at night. Lets say a security guard comes at evening 19:00 and go 04:00 in the morning.
How to put the same Logic 1 for security guards too. (SQL SQL level)
Currently, I am doing like below.
IN TIME
OUT TIME
checking if already have IN TIME
Checking if already done OUT TIME
Please suggest
All-Star
53711 Points
24042 Posts
Re: Manage attendance for night shifts : at sql level
May 19, 2020 07:54 PM|mgebhard|LINK
Seems basic enough. The user creates a clock-in record. The user must clock-out before being able to clock-in again.
If you need to catch clock-ins without clock-outs then create a schedule job that looks for a clock-in record that does not have a clock-out record.