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

[muhammadrashidmughal]

in order to check doubtful records

select employee_id,max(check_date_time) as max_check_time,check_type
from table
where Check_Date_Time>='2008-mon-day 00:00:00.000' and Check_Date_Time<='2008-mon-day 23:59:59.000'
group by Employee_Id,check_type
order by 1

[Delerna]

How about this
assuimg the table is called tblBundy

SELECT checkInOutID,
       employeeID,
       isCheckIn,
       checkInOutTime,
       isCheckInNext
FROM
(   SELECT checkInOutID,
          employeeID,
          isCheckIn,
          checkInOutTime,
          (  SELECT TOP 1 isCheckIn
             FROM tblBundy b
             WHERE a.EmployeeID=b.EmployeeID 
                  and year(a.checkInOutTime)=year(a.checkInOutTime)
                  and month(a.checkInOutTime)=month(a.checkInOutTime)
                  and day(a.checkInOutTime)=day(a.checkInOutTime)
            ORDER BY checkInOutTime
         ) as isCheckInNext
   FROM tblBundy a
)
WHERE isCheckIn=isCheckInNext
 

Of course if there are a lot of records this could be quite slow.
To get around that perhaps you could have a stored proc that has from and to date parameters.
The storerd proc would then save the records from the main table, that are within the date range, into a table variable and run the above query using the table variable and return the suspect records in that date range.
By the way I haven't tested the above query. Its straight from my mind into this post so there may be some bugs