Get last action of a person on a specific date

Question

I have a table with the following rows:

 NAME                        RFID                  ACTION    TIME       DATE

Kashif Islam            E2001026770D00742340248A    OUT  12:40:00   1/30/2013
Dr.Arshad Ali Shahid    E2001026770D0212267009D3    IN   13:52:00   1/30/2013
Qasim Mehmood           E2001026770D018223202774    IN   13:52:00   1/30/2013
M.Bilal Khan            E2001026770D009522402D80    IN   13:52:00   1/30/2013
Abdul Hameed            E2001026770D0181248019B8    IN   13:52:00   1/30/2013
Usman Tariq             E2001026770D00862570111D    IN   13:52:00   1/30/2013
Dr. Asif Gondal         E2001026770D012426600B32    IN   13:52:00   1/30/2013
Dr.Arshad Ali Shahid    E2001026770D0212267009D3    OUT  13:52:00   1/30/2013
Dr.Arshad Ali Shahid    E2001026770D0212267009D3    IN   13:53:00   1/30/2013
Dr.Arshad Ali Shahid    E2001026770D0212267009D3    OUT  13:53:00   1/30/2013
Dr.Arshad Ali Shahid    E2001026770D0212267009D3    IN   13:53:00   1/30/2013
Dr.Arshad Ali Shahid    E2001026770D0212267009D3    OUT  13:53:00   1/30/2013
Dr.Arshad Ali Shahid    E2001026770D0212267009D3    IN   13:54:00   1/30/2013
Dr.Arshad Ali Shahid    E2001026770D0212267009D3    OUT  13:54:00   1/30/2013
Aamir Hafeez            E2001026770D01952510155B    IN   13:55:00   1/30/2013
Amjad Ali Anjum         E2001026770D024125401476    IN   13:55:00   1/30/2013
Amjad Ali Anjum         E2001026770D024125401476    OUT  13:56:00   1/30/2013
Dr. Asif Gondal         E2001026770D012426600B32    OUT  13:56:00   1/30/2013
Arif Shah               E2001026770D01852370206D    IN   13:56:00   1/30/2013
Fida ul Hassan          E2001026770D02112720095C    IN   13:56:00   1/30/2013

Now I want to get the latest ACTION on a given date for each person, e.g.:

    1/30/2013   13:56:00    OUT Amjad Ali Anjum
    1/30/2013   13:56:00    IN  Arif Shah
    1/30/2013   13:56:00    OUT Dr. Asif Gondal
    1/30/2013   13:54:00    OUT Dr.Arshad Ali Shahid
    1/30/2013   13:56:00    IN  Fida ul Hassan

I am using the below query to get it, but I am not getting correct results:

WITH CTE AS (
SELECT 
   row_number() over (partition by personname order by date) rn,
   date,action,time,
   personname

FROM
   AISDb)
SELECT date,time,action,
       personname
FROM CTE WHERE RN = 1 AND datestamp = '1/30/2013'

Answer 1

I think you just had the filter in the wrong place and the order by inside the over() clause was all wrong.

;WITH CTE AS (
  SELECT 
   row_number() over (partition by personname order by time DESC) rn,
   date,action,time,
   personname
FROM
   AISDb
WHERE datestamp = '20130130')
SELECT date,time,action,
       personname
FROM CTE WHERE RN = 1;

SQL fiddle demo

If you need to break a tie in the case of IN/OUT somehow both recorded at the same time, one way is to break the tie assuming that OUT happened last:

;WITH CTE AS (
  SELECT 
   row_number() over (partition by personname order by time DESC, action DESC) rn,
   date,action,time,
   personname
FROM
   AISDb
WHERE datestamp = '20130130')
SELECT date,time,action,
       personname
FROM CTE WHERE RN = 1;

SQL fiddle demo