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

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

Thnx dineshasanka.
but thts not the solution to the problem
I have to retrieve all suspect(consecutive duplicate check In/Out) entries of single employee.
If there exist alternate records of checkin/out, then there is no suspect entry in the table.
When a user mistakenly checks in more than one time or checks out more than one time, the entry becomes suspicious.

[quote user="Nazish Kanwal"]
table definition is as follows:
checkInOutID (PK,int,not null)
employeeID (FK, int, not null)
isCheckIn (bit, not null)
checkInOutTime (datetime, not null)

[/quote]
Is CheckInOutID an Identity Column ? OR Are you storing consecutive number in the column ? If yes then
Select A.CheckInOutID, A.EmployeeID, A.IsCheckIn, A.CheckInOutTime
From <your tablename> AS A
JOIN <your tablename> AS B
ON A.EmployeeID = B.EmployeeID
AND A.IsCheckIn = B.IsCheckIn
AND A.CheckInOutTime = B.CheckInOutTime -- Please check whether you are entring same time for both consecutive entries else remove this cond.
AND A.CheckInOutID = B.CheckInOutID - 1

Hi,
column checkInoutID in your table is Identity column or you manually insert it by adding +1 per employee.By knowing it we could write query to compare immediate record.
Aother solution could be to compare count of checkin entries when 1 and when 0 to find out all the employees under scrutiny.

checkInOutID is an identity column.
I have to retrieve all suspect entries of one employee at a time.
1 thing i wud like to mention here is that, comparing counts of all true and false entries of a particular employee won't be appropriate, as there will always be one "true" record more than a "false" record if employee is on job right now(he has not checked out till now).

Thanks in Advance.

Hi,
Try the following...it should return all suspect records.
create table #Test
(
checkInOutID int identity not null
, employeeID int not null
, isCheckIn bit not null
, checkInOutTime datetime not null
)
insert into #Test(employeeID,isCheckIn,checkInOutTime)
values(1,1,'1 Jan 2008 10:23')
insert into #Test(employeeID,isCheckIn,checkInOutTime)
values(1,0,'1 Jan 2008 15:34')
insert into #Test(employeeID,isCheckIn,checkInOutTime)
values(2,1,'1 Feb 2008 08:14')
insert into #Test(employeeID,isCheckIn,checkInOutTime)
values(2,1,'2 Feb 2008 08:15')
insert into #Test(employeeID,isCheckIn,checkInOutTime)
values(1,0,'2 Jan 2008 23:45')
insert into #Test(employeeID,isCheckIn,checkInOutTime)
values(3,1,'15 Jan 2008 07:00')
insert into #Test(employeeID,isCheckIn,checkInOutTime)
values(3,0,'15 Jan 2008 19:00')
select *
from #Test t1
where t1.isCheckIn =
(
select top 1
t2.isCheckIn
from #Test t2
where t2.employeeID = t1.employeeID
and t2.checkInOutTime >= t1.checkInOutTime
and t2.checkInOutID <> t1.checkInOutID
order by t2.checkInOutTime asc
)
or t1.isCheckIn =
(
select top 1
t2.isCheckIn
from #Test t2
where t2.employeeID = t1.employeeID
and t2.checkInOutTime <= t1.checkInOutTime
and t2.checkInOutID <> t1.checkInOutID
order by t2.checkInOutTime desc
)
order by t1.employeeID

drop table #Test
Hope this helps...

Hurrah!!!!! Live Long Martins!!!![*]
u really understood and solved my problem!![Y]
Thnx for all contributers including Martins []
Regards,
Nazish

No problem Nazish []
Just check the query again, because I edited it to change the order by in the first subquery.

Sorry Martins
ur query did not work for all data. but i got the solution of my problem from another post in a forum.
Here it is to share with all:
WITH CTE AS
(SELECT checkInOutID,
employeeID,
isCheckInOut,
timeInOut,
ROW_NUMBER() OVER(PARTITION BY employeeID ORDER BY checkInOutID) -
ROW_NUMBER() OVER(PARTITION BY employeeID,isCheckInOut ORDER BY checkInOutID) AS grp
FROM EMPLOYEE_CHECKINOUT),
CTE2 AS
(SELECT checkInOutID,
employeeID,
isCheckInOut,
timeInOut,
COUNT(*) OVER(PARTITION BY employeeID,isCheckInOut,grp) AS grpcnt
FROM CTE)
SELECT checkInOutID,
employeeID,
isCheckInOut,
timeInOut
FROM CTE2
WHERE grpcnt>1
ORDER BY checkInOutID