i did it and here the solution (newbies solution for sure)
- i declared all the variables i need
DECLARE @OLD_ENTNO decimal(18,0)
DECLARE @OLD_IN varchar(10)
DECLARE @NEW_IN_TIME datetime
DECLARE @NEW_IN varchar(10)
DECLARE @USERID varchar(10)
DECLARE @DEPTID varchar(10)
DECLARE @BRNCHID varchar(10)
- filled the variables
SET @OLD_ENTNO = (SELECT TOP 1 ENTNO FROM CLOCKING WHERE EMP_NO = (SELECT USERID FROM INSERTED) ORDER BY DATE_TIME_IN DESC)
SET @OLD_IN = CONVERT(varchar(10),(SELECT DATE_TIME_IN FROM CLOCKING WHERE ENTNO = @OLD_ENTNO),120)
SET @NEW_IN_TIME = (SELECT PROCESSTIME2 FROM INSERTED)
SET @NEW_IN = CONVERT(varchar(10), (SELECT PROCESSTIME2 FROM INSERTED),120)
SET @USERID = CONVERT(varchar(10), (SELECT USERID FROM INSERTED),120)
SET @DEPTID = '0'+ CONVERT(varchar(10), (SELECT DEPARTMENTID FROM INSERTED),120)
SET @BRNCHID = '0'+ SUBSTRING(@DEPTID,1,1)
- used if condition
IF (@OLD_IN = @NEW_IN)
BEGIN
UPDATE CLOCKING SET DATE_TIME_OUT = (SELECT PROCESSTIME2 FROM INSERTED), STATUS = 'OUT' WHERE ENTNO = @OLD_ENTNO
END
IF (@OLD_IN < @NEW_IN)
BEGIN
INSERT INTO CLOCKING (EMP_NO,BRANCH_NO,DEPT_NO,DATE_TIME_IN,STATUS) VALUES (@USERID,@BRNCHID,@DEPTID,@NEW_IN_TIME,'IN')
END