locked
Manage attendance for night shifts : at sql level RRS feed

  • Question

  • 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

    Tuesday, May 19, 2020 6:16 PM

Answers

  • User475983607 posted

    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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 19, 2020 7:54 PM