I find myself annoyed with my younger self as I slough through multiple views and queries to slightly change some things so that I can get a similar result set with slightly different customer requirements.

Simply put, in this case, the main issue is that I have 2 silos of multi-layered SQL views aggregating two different things. A quantity of an item used, and the number of hours employees worked with those quantities. Ultimately we arrive at a productivity number of hours / widgets made.

The real time version of this includes scheduled hours and widgets as well as actual hours and widgets, combining the results in the final output.

I was asked to make a copy of this report, BUT instead of a history up to the current date of the report run, I need to limit it to only widgets produced up through the last Saturday.

After an initial copy of the report to format it a little differently based on different info presented and groupings changing, I got the format and sat down and cracked my knuckles to have at the SQL query. Well querIES as I mentioned in multiple silos.

Ultimately, I was dealing with 4 queries in one silo I needed to update with slightly different fields and query criteria. Eventually at that 4th level was where I could specify the date at which we were limiting the data.

I still need to go back to the other silo to also modify the date there. Ultimately it’s not that hard, but it is time consuming because of the complexity of the queries and SQL Server’s propensity to not leave my formatting nicely tabbed and separated as I originally had it.

A couple of tips for my future self:

• The naming of the views was helpful and made sense as to what data they were retrieving. This was good.
• For future reference, in complex queries it would be helpful to put complex calculations into SQL functions with names that made sense. This would simplify the views and make them easier and more straightforward to understand. As it is I had multiple nested case statements which was quite annoying. However, I’m not sure how functions would affect performance. I know they can severely impact Access when trying to use global VBA functions in queries. This would have to be user functions within SQL Server.
• In a similar vein, I could make these stored procedures and pull the information into local SQL Server temp tables, similar to the way I might to speed up and make Access queries less complex. This could be akin to using VBA to manage the overall data flow of a particular report or process. This could make it easier for testing and duplication and modifications. With stored procedures and / or VBA there is the potential added benefit of reusing the same code for multiple pieces.