Ich habe die folgende Tabellenstruktur:

Artike

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

Veranstaltun

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

Die beiden Tabellen werden nur durch den Primärschlüssel von Item und den Bereich von ItemStart und ItemEnd (einschließlich) in Event verknüpft. Ereignisse beziehen sich immer auf zusammenhängende Abfolgen von Elementen, aber nicht alle Ereignisse für ein bestimmtes Element haben den gleichen Bereich. Ereignisse treten für ein bestimmtes Element niemals am selben Datum auf.

Die Abfrage, die ich erstellen möchte, lautet wie folgt:

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

Beispielausgabe:

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)

Das scheint auf den ersten Blick vernünftig zu sein, und wenn es traditionelle Fremdschlüsselbeziehungen gäbe (stellen Sie sich vor,ItemID anstattItemStart undItemEnd) Ich würde wahrscheinlich eine Verknüpfung zu einer korrelierten Unterabfrage erstellen:

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

Aber mit der festgefahrenen Bereichsbeziehung möchte ich etwas Ähnliches tun, aber es funktioniert nicht:

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

Ich erhalte eine Fehlermeldung zui.ID >= e_max.ItemStart AND i.ID <= e_max.ItemEnd in Zeile 6, da Sie nicht auf @ verweisen könni aus einem anderen Teil des Joins. Ich wollte das tun (was im einfacheren Beispiel nicht erforderlich ist), weil ich beim Erstellen der Unterabfrage keine einzige ID mehr zum Verknüpfen habe - die überlappenden Bereiche bedeuten, dass es viele Möglichkeiten gibt, ein einzelnes Element einzuschließen Daher möchte ich direkt auf diesen Artikel verweisen, dessen ID nur in der Artikel-Tabelle der obersten Ebene verfügbar ist.

Ich hoffe das ergibt Sinn

Ich verwende SQL Server 2008 R2. Dies ist für einen Bericht gedacht, der über Nacht ausgeführt wird. Geschwindigkeit ist also nicht so wichtig, wie sie sein könnte, aber es gibt sehr viele Elemente (Hunderte von Millionen). Es gibt zwar mehrere Ereignisse für jedes Objekt, aber die Verwendung großer Bereiche bedeutet, dass es viel weniger Ereignisdatensätze gibt.

Things, über die ich nachgedacht habe:

Eine Erweiterung der Beziehung zwischen Element und Ereignis, sodass Ereignisse für alle einzelnen Elemente aufgezeichnet werden. Dies würde zu einer signifikanten Erhöhung der betrachteten Datenmenge führen, würde jedoch den einfacheren Abfrageansatz ermöglichen.Somehow verarbeiten die Ereignisse, um die Bereiche einzuschränken oder zu konsolidieren - wenn ich wüsste, dass für ein bestimmtes Element alle seine Ereignisse den gleichen Anfang und das gleiche Ende haben, könnte ich möglicherweise die Dinge vereinfachen. Nicht ganz durchdacht.

Wie kann ich diese Abfrage erstellen? Danke im Voraus