-1

Here is my table[![Biometric System Employee

  • List item

    AttendanceLogs

I am using this SP But got an error message Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. please anyone help me thank you.

IF(
     (
        SELECT(
           SELECT * FROM AttendanceLogs 
           WHERE LogDateTime < CURRENT_TIMESTAMP
           and LogDateTime > (
              select CONVERT(datetime,CURRENT_TIMESTAMP)
           )
        )
     %2)
=0)
begin
 Update AttendanceLogs set Direction=1
end
else
begin
Update AttendanceLogs set Direction=0
end

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 'Direction' column. Now I want to do is to change the Direction column from 1 to 0 If the employee 1 login at 7:27 Pm it should be recorded as '1' and when the same employee tries to login again at 7:28 Pm that same day, it should be recorded as '0'. it will be the basis to update the record

UnhandledExcepSean
  • 12,504
  • 2
  • 35
  • 51
Sakthi K
  • 1
  • 3

2 Answers2

0

You are going about this the wrong way.

You should be doing something like this:

UPDATE AttendanceLogs 
SET Direction = CASE WHEN <condition> THEN 0 ELSE 1 END

When <condition> should be replaced with your specific condition.

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • 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 'Direction' column. Now I want to do is to change the Direction column from 1 to 0 If the employee 1 login at 7:27 Pm it should be recorded as '1' and when the same employee tries to login again at 7:28 Pm that same day, it should be recorded as '0'. it will be the basis to update the record – Sakthi K Jul 27 '15 at 07:56
  • what version of sql server are you working with? what do you want to do with the duplicated records? – Zohar Peled Jul 27 '15 at 09:19
  • I am using Sql Server 2005, i want to update the ' Direction ' Column from the above table Employee First Punch is 1 (Checkin) and Second punch is 0(CheckOut) that is the Problem . please help me sir. – Sakthi K Jul 27 '15 at 09:23
  • http://stackoverflow.com/questions/12119379/stored-procedure-to-update-one-column-with-different-values , I tried this link Example sir . – Sakthi K Jul 27 '15 at 09:26
  • You have 4 records with the same employeeCode and logdatetime. what do you want to do with these records? – Zohar Peled Jul 27 '15 at 12:57
  • How to filter the Employee First punch is an Intime and then Employee Second punch is an OutTime in separate table or the same table in the above example . – Sakthi K Aug 08 '15 at 05:21
0

Read the error message:

Only one expression can be specified in the select list when the sub-query is not introduced with EXISTS

If you remove the unnecessary stuff from your IF statement you will end up with this:

 IF
    (
        SELECT *
        FROM AttendanceLogs 
        WHERE LogDateTime < CURRENT_TIMESTAMP 
        and LogDateTime >  CONVERT(datetime,CURRENT_TIMESTAMP)
    )%2
=0

So, the question is on what you are trying to apply % operator? The * is selecting the all columns and the engine does not to know on which column to apply the %2.

Also, I guess you are trying to do something like this:

UPDATE AttendanceLogs
SET Direction = IIF([MyColumn] % 2 = 0, 1, 0)
WHERE LogDateTime < CURRENT_TIMESTAMP 
    and LogDateTime >  CONVERT(datetime,CURRENT_TIMESTAMP)
gotqn
  • 42,737
  • 46
  • 157
  • 243