0

I'm having difficulties creating a stored procedure that would update rows based on employeeno, checktime and id.

Here is my table:

enter image description here

I am creating an employee attendance system. Basically we have biometrics finger scanning device to track time and stored it to database but the device was not capable of storing time as checkout it could only be checkin as shown in CheckType column.

Now I want to do is to change the Checktype column from I to O

Example :

If the employee 465-04-01 login at 7:46 am it should be recorded as I and when the same employee tries to login again at 7:47 am that same day, it should be recorded as O now, employeeno, id which is autonumber should be included since it will be the basis to update the record.

If employee logins at 4pm that same day it should be recorded as I then the next login should be O in the same day.

Can you please help me? I need a stored procedure doing this as I am not having enough experience regarding this.. please help me!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Dhenn
  • 295
  • 2
  • 7
  • 27
  • Can I ask why you store a user I'd and employee no? It looks like you only need one of these columns.. Probably user Id as I'm guessing employee no is tied to user id? – Lock Aug 25 '12 at 05:26
  • yes userid is tied with employeeno.. – Dhenn Aug 25 '12 at 05:28
  • 1
    I don't think a stored procedure is the appropriate place for the logic you're describing. It sounds like an key part of the software you're writing. The data layer shouldn't be responsible for figuring out whether the user is IN or OUT, the application should be responsible for determining that and sending the correct data to the data layer for storage. – Daniel Mann Aug 25 '12 at 05:49
  • You could use the decode function in your update query: checktype = decode(checktype, 'I','O','I') – Lock Aug 25 '12 at 05:23
  • thanks for your suggestion but I need it to be automatic.. – Dhenn Aug 25 '12 at 05:26
  • @DanielMann you have point regarding this, can you tell me how do I write this to my software? I didn't get the logic... – Dhenn Aug 28 '12 at 00:36

1 Answers1

1

Check for the CHECKTIME before current time each time an employee logs in

Use this type of Query in SP

    IF((SELECT(SELECT * FROM TBL_NAME
    WHERE CHECKTIME < CURRENT_TIMESTAMP and CHECKTIME > (select CONVERT(date,CURRENT_TIMESTAMP)))%2)=0)
    BEGIN
    --//HERE WRITE YOUR CODE SETTING CHECKTYPE AS 1 (EMPLOYEE ENTER)
    END
    ELSE
    BEGIN
    --//HERE WRITE YOUR CODE SETTING CHECKTYPE AS 0 (EMPLOYEE EXIT)
    END

It checks for no of times the recoeds are preasent are Even or Odd. If the count is Even, Then It returns as 1. It means the employee has ENTERED. Else If the count is Odd, Then It returns as 0. It means the employee has EXITED. (Here 0 is Even)

The value would be 1 when the value is Entered First Time (also When Third, Fifth, Seventh, ... Times) and would be 0 when the value is entered Second Time (also When Fourth, Sixth, Eighth, ... Times)

Krishna Thota
  • 6,646
  • 14
  • 54
  • 79
  • thanks but checktime should not be compared to a current timestamp, I just need to check whether the date and time is match with employeeno, can you do that? – Dhenn Aug 25 '12 at 11:05
  • @Dhenn I didn't get you clearly. Can you elaborate your requirement – Krishna Thota Aug 25 '12 at 11:08