That’s right, I have fallen from grace as lead developer and have been reduced to writing mundane SQL to generate spreadsheets for customers. Fortunately this is just a temporary assignment, but it’s a PITA nonetheless.
I did learn a few useful things today though. First, SQL sucks. And second, SQL sucks. Third, if you have a really complicated query that’s going to require a lot of nested tables and selects, you can really save your sanity by refactoring things into UDFs. Yeah, it’s a little dirty having UDF helpers for what amounts to an ad-hoc query, but it really makes life simpler. Consider the following (greatly simplified) example:
Task: Create a report of projects, one line per project. Each line should contain a single column with all the personnel involved, another with all the organizations involved, and a link to the project.
What we have: Projects are stored in a table. There is a one-to-many relationship to a table that contains organizations. Projects are also one-to-many related to documents. Each document is one-to-many related to a table containing people. Each document also has a URL. A project can have a lot of URLs because of this.
The (first) problem: We want to aggregate some of the one-to-many rows into a single field in the SQL output. Yeah, that would be easy/easier with MySQL and group_concat, but this isn’t MySQL, it’s SQL Server. There are ways around it, but I found that it was easier to just create UDFs for exactly what I needed, like so:
I could have done something similar using nested CTEs and other hideous garbage, but this works well enough.
The second problem: We want a *single* URL for each activity. That means we can’t just do a join between the projects and documents, because then we get multiple rows for each activity. Again, UDFs to the rescue:
And like magic, my report works!
Hopefully tomorrow will be back to non-SQL work.