使用不同值更新一列的存储过程 [英] Stored procedure to update one column with different values

问题描述

我在创建基于 employeeno、checktime 和 id 更新行的存储过程时遇到了困难.

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

这是我的桌子:

我正在创建一个员工考勤系统.基本上，我们有生物识别指纹扫描设备来跟踪时间并将其存储到数据库中，但该设备无法存储时间，因为结帐时只能按 CheckType 列所示进行签入.

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.

现在我想做的是将Checktype列从I改为O

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

示例:

如果员工 465-04-01 在早上 7:46 登录，则应记录为 I，当同一员工在同一天早上 7:47 再次尝试登录时，应记录为 I现在记录为O，employeeno, id 应该包括自动编号，因为它将作为更新记录的基础.

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.

如果员工在当天下午 4 点登录应该记录为 I 那么下次登录应该是 O 在同一天.

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!

推荐答案

每次员工登录时检查当前时间之前的CHECKTIME

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

在 SP 中使用这种类型的查询

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

它检查记录出现的次数是偶数还是奇数.如果计数为偶数，则返回1.这意味着该员工已进入.否则如果计数为奇数，则返回0.这意味着该员工已退出.(这里 0 是偶数)

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)

当值是第一次输入时(还有第三次、第五次、第七次、...次)，该值将是 1 并且当值是 0进入第二次(也当第四次、第六次、第八次、...次)

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)

这篇关于使用不同值更新一列的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！