Retrieving duplicate consecutive records.(How to make query for this scenario?)

[NazishKanwal]

Hi all

I have a table in which I save a record each time when a user checks in and checks out the

office through a time in/ time out device(this device takes thumb impression of user, thus

inserts record)

table definition is as follows:
checkInOutID (PK,int,not null)
employeeID (FK, int, not null)
isCheckIn (bit, not null)
checkInOutTime (datetime, not null)

where ischeckIn is "true" if user checks in and "false" if user checks out.

if there are alternative checkin/check out records in the table then it means that there's

no suspect entry of user. but if there are any multiple consecutive records of check in or

check out, then all those entries will be considered to be suspicious.

PLEASE HELP ME IN MAKING A QUERY OR A STORED PROCEDURE TO RETRIEVE ALL THOSE SUSPECT

ENTRIES.

I will be extremely grateful!!

Regards,
Nazish

[debasisdas]

I am bit confused with your logic. Not sure what do you mean by that suspect.

[NazishKanwal]

I am bit confused with your logic. Not sure what do you mean by that suspect.

****************************************************************************

I have a table in which I save a record each time when a user checks in and checks out the office through a time in/ time out device(this device takes thumb impression of user, thus inserts record)

table definition is as follows:
checkInOutID (PK,int,not null)
employeeID (FK, int, not null)
checkInOut (bit, not null)
timeInOut (datetime, not null) {below is the proper definition of that table}

where checkInOut is "true" if user checks in and "false" if user checks out.

if there are alternative checkin/check out records in the table then it means that there's no suspect entry of user. but if there are any multiple consecutive records of check in or check out, then all those entries will be considered to be suspicious.

CREATE TABLE EMPLOYEE_INFO
(
employeeID INT IDENTITY(1,1) PRIMARY KEY,
employeeName VARCHAR(100)
)

--===== Create the checkin/out table with

CREATE TABLE EMPLOYEE_CHECKINOUT
(
checkInOutID INT IDENTITY(1,1) PRIMARY KEY,
employeeID INT FOREIGN KEY(FROM EMPLOYEE_INFO TABLE),
checkInOut BIT,
timeInOut DATETIME
)

--===== Insert the test data into the EMPLOYEE_INFO table

INSERT INTO EMPLOYEE_INFO
(
employeeName
)
SELECT 'Nazish' UNION ALL
SELECT 'Bob' UNION ALL
SELECT 'Jill'

--===== Insert the test data into the EMPLOYEE_CHECKINOUT table

INSERT INTO EMPLOYEE_CHECKINOUT
(
employeeID,
checkInOut,
timeInOut
)
SELECT 1,1,gettime() UNION ALL
SELECT 1,0,gettime() UNION ALL
SELECT 1,1,gettime() UNION ALL --suspicious entry
SELECT 1,1,gettime() UNION ALL --suspicious entry
SELECT 1,1,gettime() UNION ALL --suspicious entry
SELECT 1,0,gettime() UNION ALL
SELECT 2,1,gettime() UNION ALL
SELECT 2,0,gettime() UNION ALL --suspicious entry
SELECT 2,0,gettime() UNION ALL --suspicious entry
SELECT 2,1 gettime() UNION ALL
SELECT 3,1,gettime() UNION ALL
SELECT 3,0,gettime()

To get all those suspicious entries, I wrote the following query:

select *
from EMPLOYEE_CHECKINOUT t1
where t1.checkInOut =
 (
  select  top 1
   t2.checkInOut
  from EMPLOYEE_CHECKINOUT t2
  where t2.employeeID = t1.employeeID
   and t2.timeInOut > t1.timeInOut
   and t2.check_sk_seq <> t1.check_sk_seq
  order by t2.timeInOut desc
 )
 or t1.CheckInOut =
 (
  select  top 1
   t2.checkInOut
  from EMPLOYEE_CHECKINOUT t2
  where t2.employeeID = t1.employeeID
   and t2.timeInOut <= t1.timeInOut
   and t2.check_sk_seq <> t1.check_sk_seq
  order by t2.timeInOut desc
 )
order by t1.employeeID

but this query is not giving what I really wanted to get. It’s not getting me the correct suspicious entries.Should I use any other logic e.g. by using cursor???
Plz HELP ME OUT!! :(

[mIrfan]

Hello Naz,

Can't we consdier all the entries lying b/w MIN (timeinout for checkinout =1) AND MAX (timeinout for checkinout = 0). I believe all entries b/w these timigs will become automatically suspecious. Just a thought. Following is the query that is based on the my thougth. According the the data you have provided in its giving correct resuts. i.e 4 entries for empid = 1 and 2 for empid = 2. You can checkit out if you like.

If still its not getting you what you want them perhaps I did get you properly. Let me in either case. Thanks.

SELECT emp1.employeeid, emp1.checkinout,
case
WHEN emp1.checkinout = 1 And emp1.checkinout = emp2.checkinout AND emp1.timeinout > emp2.CheckInTime THEN emp1.timeinout
WHEN emp1.checkinout = 0 And emp1.checkinout = emp2.checkinout AND emp1.timeinout < emp2.CheckOutTime THEN emp1.timeinout
END AS SuspeciousEntries

FROM EMPLOYEE_CHECKINOUT AS emp1
INNER JOIN
(SELECT employeeid, checkinout,
CASE
when checkinout = 1 THEN MIN(timeinout)
END As CheckInTime,
CASE
WHEN checkinout = 0 THEN MAX(timeinout)
END AS CheckOutTIme
from employee_checkinout
group by employeeid, checkinout) AS emp2
ON emp1.employeeid = emp2.employeeid