SQL Server Performance

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

Discussion in 'SQL Server 2005 General Developer Questions' started by Nazish Kanwal, Feb 25, 2008.

  1. Nazish Kanwal New Member

    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
  2. dineshasanka Moderator

    SELECT checkInOutID ,employeeID ,isCheckIn,checkInOutTime
    FROM TABLE
    GROUP BY
    checkInOutID ,employeeID ,isCheckIn,checkInOutTime
    HAVING COUNT(*) > 1
  3. Nazish Kanwal New Member

    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.

  4. patel_mayur New Member

    [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
  5. ranjitjain New Member

    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.
  6. Nazish Kanwal New Member

    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.
  7. martins New Member

    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...
  8. Nazish Kanwal New Member

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

  9. martins New Member

    No problem Nazish [:)]
    Just check the query again, because I edited it to change the order by in the first subquery.
  10. Nazish Kanwal New Member

    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

Share This Page