Greetings All,

Ok, some of you are complaining about the use of the LEFT JOIN. Please allow me to explain. When I am running some reports on the data, I am requested to show which ones fall into certain date ranges and which ones fall into different date ranges. The final set of records will end up having some flags on them for reporting purposes. None of the records are to be excluded just because they don't fall within the date check. So, I can't just throw them away in the script by doing a regular JOIN. A record that does not match one criteria may match another and so must still be included.

This example used temporary tables and created a calendar table. My regular script DOES NOT!!! That was done only to show the logic behind the idea here and to create some test data. If you want to test this better, then you will need to create your own regular table that has a start and end date in it and a ton of records. I also have to be careful what I post for scripts and data. As many of you already know, security and privacy are very important. So, I need to show this with completely different tables and names but still show the logic. I can't show you the real scripts or real data, it has to be completely different or else I get into serious trouble (lose job and/or jail time). I really don't want to have to create a whole test database just to show one idea. If I did that, it would make the attachments on my articles a lot larger.

I am also dealing with 4 or 5 different tables that are linked together for my reports. These tables will be using "Primary Keys" that range between 4 to 7 fields in size. Not a lot of fun to write scripts on in my book. But, I inherited this database when I received this job from the prior DBA. I can't just change the layout of the database as I wish. I have to take what exists and just make it work. Sort of like when the street crew has your main highway closed for construction and you have to find a way through the back roads to get to work. Your boss usually doesn't care about the transportation hurdles, he just wants you at your desk on time. You can't just use the fast highway like you want so you have to be inventive with what you have.

This logic was only a small portion of a much larger script I had to do a major report. But, this also was the biggest slowdown in that report, (the BETWEEN dates). This one change made the report run a lot faster. Everything else I had tried, including adding several different indexes, did not. The report had to analyze the data on several levels so sometimes it's not easy to find just the right index. When I did find a way that worked very well, I wanted to share that idea with others to help them too. Showing the original script would not be good as this one idea would have been lost in the complexity of the entire script. So, splitting it out into a smaller script to highlight the one idea and adding some test data was hopefully a bit better. If you have some ideas on how to write this better, then fine, please share. I will always be a learning DBA and am willing to accept constructive advise.

Have a good day.

Terry Steadman