Update Automation in SQL-Server

Question

I am doing a project called "Attendance Management System"

The company has its own biometric finger scanner who checks for employee's attendance and stored in on a database table.

The table consist of 4 fields (ID autonumber, EmployeeNo, CheckTime and CheckType)

The Timekeeping system store all the employee's logs and attendance but it doesn't specify whether the checktype is "I" or "O". As you can see the image below, everytime the employee log in, the system store it to database automatically but it stored as "I", and when the same employee log in again it will store as "I" again.

My client want it to be automated as possible. He wants to changed the checktype to "O" once the employee login again.

For example:

I am an employee. I am going to log in at today 8am, then it will store to database as

today 8AM with checktype "I".. since I am not aware that I have logged in already, I loged in again for the second time at 8:04 AM.. the system stores it as "I" again the same as if I am going to logout at 5PM, the system also record it as "I"..

I would like this to update the database automatically.Without any parameters provided by the user. I want it to be in a stored procedure to automate the process.

It should be restricted within the same employee: Example:

Employee1 logged at today 8am.. (checktype "I") Employee1 logged again at today 11am... (checktype "O") employee1 logged again at today 12pm.. (checktype "I") employee1 logged again at today 4pm (checktype "O")

Criterias should be restricted within employeeno, checktime only.. and would like to update checktype also based on given datas.

The old system is not capable storing logged ins as "O" it was purely "I"..

How to do this? I need your inputs and help badly! Please?

I have created a stored procedure but do not know where I can start it and I dont have the idea to finish the procedure and make it work..

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE AUTOMATIC_OVERRIDE
    @ID INT,
    @EMPLOYEENO NVARCHAR(20),
    @CHECKTIME DATETIME,
    @CHECKTYPE NVARCHAR(1)

AS
    SET NOCOUNT ON;
    WHILE @ID = (SELECT ID FROM CHECKTIME)

        SELECT ID,EMPLOYEENO,CHECKTIME,CHECKTYPE
          FROM CHECKTIME
         GROUP BY ID,EMPLOYEENO,CHECKTIME,CHECKTYPE
         ORDER BY CHECKTIME
GO

Please help me to solve this problem. Thanks.

Answer 1

EDIT:

The solution into the PRC:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE AutomaticOverrideByID
    @ID INT,
    @EMPLOYEENO NVARCHAR(20)

AS

    UPDATE CHECKTIME
    SET CHECKTYPE = CASE WHEN (SELECT
    COUNT(ID) AS CountOfLogin
    FROM CHECKTIME
    WHERE CHECKTIME.EMPLOYEENO = @EMPLOYEENO 
    AND CHECKTIME.CheckTime < C.CheckTime) % 2 = 1 THEN 'O' ELSE CHECKTYPE END
    FROM CHECKTIME C
    WHERE C.ID = @ID

GO

And here is an UPDATE to the existing dataset as Dhenn asked:

UPDATE CHECKTIME
SET CHECKTYPE = CASE WHEN (SELECT
COUNT(ID) AS CountOfLogin
FROM CHECKTIME
WHERE CHECKTIME.EMPLOYEENO = C.EMPLOYEENO 
AND CHECKTIME.CheckTime < C.CheckTime) % 2 = 1 THEN 'O' ELSE CHECKTYPE END
FROM CHECKTIME C

SQL Fiddle attached.

Answer 2

try this option (SQL Fiddle code), should be valid for MS SQL Server 2000.

And here you have the full working code, copy & paste on a SSMS tab. Use it to create your procedure.

DECLARE @Checktime TABLE([ID] int, [EmployeeNo] NVARCHAR(5), [CheckTime] datetime, [CheckType] NVARCHAR(1));

INSERT INTO @Checktime
    ([ID], [EmployeeNo], [CheckTime], [CheckType])
VALUES
(1, '111-1', '20120826 08:00:00', 'I'),
(2, '222-2', '20120826 08:05:00', 'I'),
(3, '333-3', '20120826 08:07:00', 'I'),
(4, '444-4', '20120826 08:09:00', 'I'),
(5, '555-5', '20120826 08:10:00', 'I'),
(6, '666-6', '20120826 08:12:00', 'I'),
(591, '050-71-1', '20120703 13:06:18', 'O'),
(592, '050-71-1', '20120703 13:06:16', 'O'),
(593, '050-71-1', '20120703 12:54:01', 'O'),
(594, '050-71-1', '20120703 12:53:59', 'O'),
(617, '050-71-1', '20120703 07:53:14', 'O'),
(631, '050-71-1', '20120702 17:36:12', 'O'),
(641, '050-71-1', '20120702 12:13:50', 'O'),
(642, '050-71-1', '20120702 12:13:49', 'O'),
(646, '050-71-1', '20120702 12:00:33', 'O'),
(647, '050-71-1', '20120702 12:00:31', 'O'),
(678, '050-71-1', '20120702 08:07:50', 'O'),
(679, '050-71-1', '20120828 18:07:48', 'O');

DECLARE @Employee NVARCHAR(5)='050-71-1'

INSERT INTO @Checktime ([ID], [EmployeeNo], [CheckTime], [CheckType])
SELECT TOP 1 680 'NewId', EmployeeNo, GETDATE(),
       'NewCheck' = CASE 
           WHEN DATEDIFF(dd, CheckTime, GETDATE())>0 THEN 'I'
           WHEN CheckType = 'I' THEN 'O'
           ELSE 'O'
       END
  FROM @Checktime
 WHERE EmployeeNo = @Employee
 ORDER BY CheckTime DES

SELECT * FROM @Checktime

Result:

ID  EmployeeNo          CheckTime          CheckType
1     111-1     2012-08-26 08:00:00.000       I
2     222-2     2012-08-26 08:05:00.000       I
3     333-3     2012-08-26 08:07:00.000       I
4     444-4     2012-08-26 08:09:00.000       I
5     555-5     2012-08-26 08:10:00.000       I
6     666-6     2012-08-26 08:12:00.000       I
7     222-2     2012-08-28 09:37:41.917       O

Answer 3

I think it is better to use CheckIN & CheckOut columns instead of CheckTime & CheckType .

• If he logged in already , update the checkOut time .

• If he didn't log in , save the CheckIN & CheckOut time and it well be the same time in this case .

It can't be easer than this .