Thanks for the reply, really appreciate it. I think you are correct regarding a date table and i'd normally have something like that in a data warehouse design. My opinion is that doing stuff like this with an OLTP system is a bit wonky but it is what it is.

I also discovered that there was duplicate data between the ARCHIVE and PROD databases, this was throwing my numbers way off for certain date ranges. The biggest problem I have is that I only have access to the DEV environment and not production, didn't realize that they were out of synch. I was put on this project rather quickly and didn't have a chance to ask all of these questions ahead of time, gotta love dev'ing on the fly!