Querying An Event’s Product

Product relationships are defined by PU_Id and time. For example, the find the product associated with a production event a join on PU_Id and time must be made.

SELECT	e.Event_Num, 
        e.TimeStamp, 
        p.Prod_Code
FROM Events e
INNER JOIN Production_Starts ps ON e.PU_Id = ps.PU_Id
                                   AND e.TimeStamp > ps.Start_Time 
                                   AND (	e.TimeStamp <= ps.End_Time
                                                OR ps.End_Time IS NULL) 
INNER JOIN Products p ON ps.Prod_Id = p.Prod_Id
WHERE	e.PU_Id = @PUId
        AND e.TimeStamp > @ReportStartTime 
        AND e.TimeStamp <= @ReportEndTime