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

SELECT checkInOutID ,employeeID ,isCheckIn,checkInOutTime

FROM TABLE

GROUP BY  

 checkInOutID ,employeeID ,isCheckIn,checkInOutTime

HAVING COUNT(*) > 1

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.

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. 

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)

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,

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!!!!

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