469,308 Members | 2,691 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,308 developers. It's quick & easy.

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
Feb 25 '08 #1
2 1964
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
Feb 29 '08 #2
Delerna
1,134 Expert 1GB
How about this
assuimg the table is called tblBundy
Expand|Select|Wrap|Line Numbers
  1. SELECT checkInOutID,
  2.        employeeID,
  3.        isCheckIn,
  4.        checkInOutTime,
  5.        isCheckInNext
  6. FROM
  7. (   SELECT checkInOutID,
  8.           employeeID,
  9.           isCheckIn,
  10.           checkInOutTime,
  11.           (  SELECT TOP 1 isCheckIn
  12.              FROM tblBundy b
  13.              WHERE a.EmployeeID=b.EmployeeID 
  14.                   and year(a.checkInOutTime)=year(a.checkInOutTime)
  15.                   and month(a.checkInOutTime)=month(a.checkInOutTime)
  16.                   and day(a.checkInOutTime)=day(a.checkInOutTime)
  17.             ORDER BY checkInOutTime
  18.          ) as isCheckInNext
  19.    FROM tblBundy a
  20. )
  21. WHERE isCheckIn=isCheckInNext
  22.  
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
Feb 29 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Jon | last post: by
1 post views Thread by John M | last post: by
2 posts views Thread by P B via AccessMonster.com | last post: by
2 posts views Thread by lewie | last post: by
4 posts views Thread by alltechsolutions.net | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.