Event History

When retrieving inventory value that is in the past, Event_History and Event_Detail_History tables are used, as values in Events and Event_Details tables only reflect current inventory values. There is no enforced relation between the Event and Event_History tables. Event table stores the most current information for events while Event_History table stores histories of all events. If an event is deleted, there will be no record in the Event table but histories of that deleted event can be obtained from Event_History table.

In order to lookup the history of a particular event, Event_Id can be used, as this is the only column that exists in Event, Event_Details, Event_History, and Event_Detail_History tables. Based on table diagrams below, Event_History and Event_Detail_History tables do not have any primary or unique key, while Event_Id is the primary key in both Event and Event_Details tables. It is possible to have more than one record in Event_History table for a specific Event_Id. As mentioned before, Event_History stores all past records on a particular event. In order to look for a particular event for a past time frame, Modified_On column is often used with the Event_Id column in the query as this provides the time order.

Following is a sample query getting all available events that are 5 days ago but not older than 30 days for PU_Id 1:

SELECT eh.*
FROM Event_History eh WITH (NOLOCK),
     (SELECT Event_Id, MAX(Modified_On) 'ModifiedOn' 
     FROM Event_History WITH (NOLOCK)
     WHERE Start_Time < DATEADD(day, -5, GETDATE())
      AND (Timestamp > DATEADD(day,-30,GETDATE()) OR Timestamp IS NULL) 
      AND PU_id=1
    GROUP BY Event_Id) r 
WHERE eh.Event_Id = r.Event_Id
      AND eh.Modified_On = r.ModifiedOn

The key is to get the last updated record for the event using MAX(Modified_On) within a given time frame for every event.