For this question assume the following:
- There is a table of events [Event]
- There is a table of employees [Employee]
- There is a table that relates the two [EventEmployee] (EventID + EmployeeID)
- Each event is assigned to n employees
- These events can occur at random dates/times
I need to query for the events that start and/or end within a specified time frame. I also need to include the first event prior to the specified date range PER EMPLOYEE assigned to the event.
For example, if I query from 03/01/2014 to 04/01/2014 and I return 15 events with 5 employees assigned to each. I also need to return the most recent event prior to 03/01/2014 for each of those employees.
I am currently accomplishing this by:
- Getting all of my events for the date range into a temp table
- Selecting everything out of the temp table
- UNION with a query that gets the DISTINCT [EmployeeID]s based on the temp table and does a CROSS APPLY on [Event] joined with [EventEmployee] to get the most recent event prior to the start date of the range.
Is there a more efficient way to accomplish this task?
Please tell me if some sample code would help the explanation.
EDIT: My Query:
Assuming @StartDate and @EndDate are DATETIME parameters,
-- Get all events that start or end within a given time frame SELECT e.[EventID], e.[EventStart], e.[EventEnd], e.[EventTypeID] INTO #EventTemp FROM [Events] AS e WHERE ( e.[EventStart] >= @StartDate AND e.[EventStart] <= @EndDate ) OR ( e.[EventEnd] >= @StartDate AND e.[EventEnd] <= @EndDate ) SELECT #EventTemp.[EventID], #EventTemp.[EventStart], #EventTemp.[EventEnd], #EventTemp.[EventTypeID] FROM #EventTemp UNION SELECT PrevEvent.[EventID], PrevEvent.[EventStart], PrevEvent.[EventEnd], PrevEvent.[EventTypeID] FROM -- Get a DISTINCT list of employeeIDs from those events ( SELECT DISTINCT ee.[EmployeeID] FROM #EventTemp INNER JOIN [EventEmployee] AS ee ON #EventTemp.[EventID] = ee.[EventID] ) AS Emp -- Get the most recent prior event for each distinct employeeID CROSS APPLY ( SELECT TOP 1 e.[EventID], e.[EventStart], e.[EventEnd], e.[EventTypeID] FROM [Events] AS e INNER JOIN [EventEmployee] AS ee ON e.[EventID] = ee.[EventID] WHERE ee.[EmployeeID] = Emp.[EmployeeID] AND e.[EventEnd] < @StartDate ORDER BY e.[EventEnd] DESC ) AS PrevEvent
Lets say we end up with 4 events in the temp table #EventsTemp. Each of those events could be tied to any number of employees. For this example, lets assume between all the events in #EventsTemp we are dealing with 3 employees.
For the final select I would want to get the list of events from #TempEvents, in addition I would also want the most recent event, prior to @StartDate, that each of those 3 employees were at. If 2 of the employees were at the same event I would expect to get a single extra row for that event, plus an extra row for the 3rd employee:
EventID,EventStart,EventEnd,EventType 5,'2014-04-01 13:00','2014-04-01 15:00',1 6,'2014-04-01 16:00','2014-04-01 19:00',1 7,'2014-04-02 10:00','2014-04-02 14:00',1 8,'2014-04-02 16:00','2014-04-02 18:00',1 2,'2014-03-26 13:00','2014-03-26 15:00',1 1,'2014-03-25 10:00','2014-03-25 14:00',1
In this example, the first four rows were straight from #EventsTemp, and the last two rows are the distinct prior events attended by any combination of the employees that attended the original 4 events from #EventsTemp