Greetings All,
I have not had time to reply to your messages individually due to time constraints so I will try to shed some light here.
The calendar and temporary tables were used in this example only to create test data - nothing more. For me, I am applying this method against a regular data table that has a start and end date in it.
The environment is on an older (slower) server using SQL 2005. The database and table layout is not optimal, but I did not design it and changing it to be better is not always an option.
Since I can't just change things for the better, I have to find more inventive ways to make it work better, hence, why I came up with this start date check and a boolean flag for the end date check.
Put this against a faster server and also on SQL 2008 and yes, these speed changes may disappear. But, when walking into a database situation not of your design and this may help you.
I had tried different ways of applying an index to the data but none of them were giving me the speed enhancement that this method did. Bit then, maybe I just did not get the order of the fields correct or something. I will look at the suggestions listed here to see if any of them work too.
I enjoy learning new and different ways of solving a problem. I can list ways I had solved them here and learn from others who have better knowledge than I. I certainly am not too proud to try to say that my script doesn't stink, but this trick may still benefit another as well as the suggestions given.
Have a good day.
Terry Steadman