User1052662409 posted
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
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
