Manage attendance for night shifts : at sql level[Answered] RSS

• • Reply
  demoninside9

  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

  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.
  
• • Reply
  mgebhard

  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.