Tengo la siguiente estructura de tabla:

Articulo

ID | Name
--------
1  | Apple
2  | Pear 
3  | Banana
4  | Plum
5  | Tomato

Evento

ItemStart | ItemEnd | EventType | EventDate
--------------------------------------------
     1    |    2    |  Planted  | 2014-01-01
     1    |    3    |  Picked   | 2014-01-02
     3    |    5    |  Eaten    | 2014-01-05

Las dos tablas están vinculadas solo por la clave principal de Item y el rango de ItemStart y ItemEnd (inclusive) en Event. Los eventos siempre se refieren a secuencias contiguas de Artículos, pero no todos los Eventos para un Artículo dado tendrán el mismo rango. Los eventos nunca ocurren en la misma fecha para un artículo dado.

La consulta que me gustaría producir es la siguiente:

List all the Items, and for each Item show the most recent Event

Salida de muestra:

ID | Name   |   Event | Date
----------------------------
1  | Apple  |  Picked | 2014-01-02 (Planted then Picked)
2  | Pear   |  Picked | 2014-01-02 (Planted then Picked)
3  | Banana |  Eaten  | 2014-01-05 (Picked then Eaten)
4  | Plum   |  Eaten  | 2014-01-05 (Eaten)
5  | Tomato |  Eaten  | 2014-01-05 (Eaten)

Esto parece bastante razonable a primera vista, y si hubiera relaciones tradicionales de clave extranjera en su lugar (imagineItemID en lugar deItemStart yItemEnd) Probablemente uniría a una subconsulta correlacionada algo como esto:

SELECT Name, EventType, EventDate
FROM Item i
    INNER JOIN (
        SELECT ItemID, EventType, EventDate
        FROM Event e
        WHERE EventDate = (SELECT MAX(EventDate) FROM Event e_max WHERE e_max.ItemID = e.ItemID)
    ) latest_events ON i.ID = latest_events.ItemID

Sin embargo, con la relación de rango en su lugar, estoy atascado, quiero hacer algo más como esto, pero no funciona:

SELECT Name, EventType, EventDate
FROM Item i
    INNER JOIN (
        SELECT ItemStart, ItemEnd, EventType, EventDate
        FROM Event e
        WHERE EventDate = (SELECT MAX(EventDate) FROM Event e_max WHERE i.ID >= e_max.ItemStart AND i.ID <= e_max.ItemEnd)
    ) latest_events ON i.ID >= latest_events.ItemStart AND i.ID <= latest_events.ItemEnd

Me sale un error sobrei.ID >= e_max.ItemStart AND i.ID <= e_max.ItemEnd en la línea 6, porque no puedes hacer referenciai desde dentro de otra parte de la unión. Quería hacer eso (lo cual no es necesario en el ejemplo más simple) porque cuando estoy construyendo la subconsulta ya no tengo una sola identificación para vincular: los rangos superpuestos significan que hay muchas formas posibles de incluir un solo elemento , por lo que quiero referirme directamente a ese elemento, cuyo ID solo está disponible en la tabla de elementos de nivel superior.

Espero que tenga sentido.

Estoy usando SQL Server 2008 R2. Esto es para un informe que se ejecutará de la noche a la mañana, por lo que la velocidad no es tan importante como podría ser, pero hay muchos artículos (cientos de millones); Si bien hay múltiples eventos en cada elemento, el uso de grandes rangos significa que hay muchos menos registros de eventos.

Cosas que he pensado:

De alguna manera expandiendo la relación Artículo / Evento para que los Eventos se registren contra todos los Artículos individuales. Esto daría como resultado un aumento significativo en la cantidad de datos bajo consideración, pero permitiría un enfoque de consulta más simple.De alguna manera, procese los Eventos para restringir o consolidar los rangos; si supiera que para un Artículo dado, todos sus Eventos tienen el mismo inicio y final, tal vez podría simplificar las cosas. No lo pensé completamente.

¿Cómo puedo producir esta consulta? ¡Gracias por adelantado!