Get data across the same table

Question

I have table sort of like

Name      |DateOfEvent|EventType
----------------------------------
Smith     |10/1/2005  |New
Thomas    |1/1/2002   |Updated
Johnson   |6/1/2002   |New
Smith     |7/1/2008   |Updated
Smith     |7/1/2000   |New

I want to return rows where the event is say New and the date is before a row with the same name but the EventType is Updated with a later date.

My thought is to iterate over the table with each row with the name but that seems really inefficient. Is there a better way to do this?

Answer 1

SELECT
  e1.*
FROM
  Events e1,
  Events e2
WHERE
  e1.EventType = 'New' and
  e1.Name = e2.Name and
  e2.EventType = 'Updated' and
  e2.DateOfEvent > e1.DateOfEvent;

Or the same with join:

SELECT
  e1.*
FROM
  Events e1
INNER JOIN
  Events e2
ON
  e1.Name = e2.Name
WHERE
  e1.EventType = 'New' and
  e2.EventType = 'Updated' and
  e2.DateOfEvent > e1.DateOfEvent;

Answer 2

Maybe something along the lines of:

SELECT
   t1.Name,
   t1.DateOfEvent,
   t1.EventType
FROM table t1
LEFT JOIN table t2
   t1.Name = t2.Name
WHERE
t1.EventType = 'New' AND t1.DateOfEvent < t2.DateOfEvent AND t2.EventType = 'Updated'

This query uses Name to do the JOIN but names might not stay unique as the table grows. Introducing a primary key and parent id-s to keep track of the history will probably work better.

Answer 3

CREATE TABLE #TEST
(
    Name varchar(10),
    DateOfEvent date,
    EventType varchar(10)
)

INSERT INTO #TEST (Name, DateOfEvent, EventType)
SELECT 'Smith',     '10/1/2005',  'New' UNION ALL
SELECT 'Thomas',    '1/1/2002',   'Updated' UNION ALL
SELECT 'Johnson',   '6/1/2002',   'New' UNION ALL
SELECT 'Smith',     '7/1/2008',   'Updated' UNION ALL
SELECT 'Smith',     '7/1/2000',   'New'

SELECT NEW.* FROM 
    (SELECT Name, MAX(DateOfEvent) AS DateOfEvent
        FROM #TEST
        WHERE EventType = 'Updated'
        GROUP BY Name
    ) UPDATED
    JOIN 
    (SELECT Name, MIN(DateOfEvent) AS DateOfEvent
        FROM #TEST
        WHERE EventType = 'New'
        GROUP BY Name
    )New ON New.DateOfEvent < UPDATED.DateOfEvent AND New.Name = UPDATED.Name

Answer 4

Use:

SELECT x.name, 
       x.dateofevent,
       x.eventtype
  FROM YOUR_TABLE x
 WHERE x.eventtype = 'New'
   AND EXISTS(SELECT NULL
                FROM YOUR_TABLE y
               WHERE y.name = x.name
                 AND y.eventtype = 'Updated'
                 AND y.dateofevent > x.dateofevent)

The problem with using a JOIN (INNER or OUTER) is that if there are more than one records that are marked "updated" with a date before the "new" one -- there'll be duplicates of the x references.