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:

   1: --------------------------------------------
   2: -- Gets a string of all the distinct funding
   3: -- organizations for an activity.
   4: --------------------------------------------
   5: ALTER function [dbo].GetFundingOrgs
   6: (
   7:     @activityID uniqueidentifier
   8: )
   9: returns varchar(1000)
  10: as
  11: begin
  12:  
  13: declare @orgs as varchar(1000)
  14:  
  15: SET @orgs = ''
  16:  
  17: SELECT @orgs = @orgs + PrimaryName + ', '
  18: FROM Organizations
  19: INNER JOIN ActivityOrganizationMetadata 
  20:     ON Organizations.OrganizationID = ActivityOrganizationMetadata.Organization
  21: WHERE Activity = @activityID
  22: AND Propogated = 0
  23: GROUP BY PrimaryName
  24:  
  25: --Remove the trailing ', '
  26: IF LEN(@orgs) > 0 
  27:     SELECT @orgs = substring(@orgs, 1, LEN(@orgs)-1)
  28:  
  29: return ( @orgs )
  30:  
  31: end

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:

   1: --------------------------------------------
   2: -- Gets the original URL for the first 
   3: -- document in the activity.
   4: --------------------------------------------
   5: ALTER function [dbo].GetOriginalUrlForActivity
   6: (
   7:     @activityID uniqueidentifier
   8: )
   9: returns varchar(100)
  10: as
  11: begin
  12:  
  13: return
  14: (
  15:     SELECT TOP 1 Url FROM DocumentsToQueries
  16:     INNER JOIN Documents ON DocumentID = Document
  17:     WHERE ActivityID = @activityID
  18:     AND Url LIKE 'http%'
  19: )
  20:  
  21: end;

And like magic, my report works!

Hopefully tomorrow will be back to non-SQL work.