Today at work, I tried to find all overlapping events within a date range. The event has a start time butN possible end times contained in two different columns in a 1-N FK’d table. I joined the events table to itself and added a WHERE clause to handle the combinations of outer select’s begin and end times to inner select’s begin and end times. Then I added an outer select to return the columns I wanted to display and tried both distinct and group by to get rid of the duplicates. The data eventually needs to go in a SSRS report grouped by date so that overlapping events are visually obvious to the reader.
I could see days where there was only one event and no corresponding event so I know the query isn’t right. However with the nested selects and the group bys, the testing and debugging is tough.
I haven’t figured exactly what I want to do, but here are the options I would like to try:
1) Table variable of events in data range with singular start and singular end time (as defined by business logic) then use this to do the self join to find the overlapping events, then wrap that with another joined select that returns the columns I really want. The table variable joined to itself will be much easier to test and debug. Once I know that is working, I can add the meaningful columns for the report.
2) Make the system define the last time of the event when the event is entered. The system does this already to determine start datetime (from N possible choices) so one more calculation shouldn’t be a stretch for the developers, right? I will log a feature request for this Wednesday.
Other ideas?
[...] This post was Twitted by dfberry – Real-url.org [...]