T-SQL Tuesday: Resolutions

In my invitation to T-SQL Tuesday #014, I picked the topic “Resolutions”, clearly to play on the new year.  The only thing I have right now that could count as a New Year’s resolution is the RTFM365 project…it’s certainly going to take some stick-to-it-iveness to finish out a year of reading from BOL every day. The rest of what I have are just ongoing life goals. So why did I pick “Resolutions”, then, if I have nothing to say?

The invitation blog gave a few brief suggestions of  what to write about, but the interesting thing is how many different ways you can run with the word. When I looked at the Merriam-Webster entry for resolution, a few of the definitions stuck out as a sort of common theme:

1) the act or process of resolving: as

• a : the act of analyzing a complex notion into simpler ones …
• e : the separating of a chemical compound or mixture into its constituents
• f (1) : the division of a prosodic element into its component parts (2) : the substitution in Greek or Latin prosody of two short syllables for a long syllable g : the analysis of a vector into two or more vectors of which it is the sum
• …[and a bunch more.]

This complex circuit is made up of simple parts, and humor

Analyzing a complex notion into simpler ones. Hmm, why does that sound familiar? Familiar in a kind of SQL Servery way….

Databases

“Hey, let’s throw together a database to track our sales conferences!”  Uh, sure boss…that simple statement has the mass and temperament of 400 angry gorillas at a monster truck rally. In an earthquake. 

My job is now to take this complex idea and break it down into its simpler pieces - the data we need to store, the rules that hold the data together, the procedures that we need to run on it.  Once that’s done, everything should be oh so simple!

But of course, it’s not.

Queries

“Hey, can you get me a list of the people that’ve attended any of the conferences in the last three years? Not counting the webconferences. Or HR personnel. And we’ll need their company and contact info.” 

Again, with the “simple”-complex requests.  And again, I can’t necessarily just sit down and bang out a query. Depending on the database architecture and the request, I might need to start with two or three queries to get different sets of data, and figure out how to link them together:

• I’ll get the set of all conferences in the last three years. If I can, I’ll limit that to non-webconferences here…alternately, I can get a list of webconference IDs and do a
  SELECT conference
  WHERE ID NOT IN (SELECT ID from Webconferences)….
  (That’s fairly lousy pseudocode, but I’m assuming you’ll go with me on this.)
• I’ll also pull a list of individuals (with related info) and filter based on their job category.
• Aaaaand then link these together.

There. Database: designed. Ability to break a request into logical chunks: achieved. Now it’s easy street!

Oh, ya think so, do ya?

Reports

“Okay, Jane.” [My name's Jen, sir.] “Could you get us a report for Bob? He needs numbers on attendance over time, categories, that kind of thing.”

Well, first of all, it’s time to go talk to Bob. What will he be using this for? Is this something he’s going to sort and calculate on himself, or feed into something else? Well then, Excel or CSV. Something presentable? Well then, PDF or Word.

And what questions is he trying to answer with this report? That’ll really drive the data and the presentation.  In the end, we might have broken this down to something like this:

• Needs something in a handout format
• Prefers graphics – pie and line charts – over lists of numbers.
• Wants attendance by company (not individual) over time – that is, how many events attended per company per month, over the last five years.

Etc etc.

What this all boils down to is something I’ve heard Itzik Ben-Gan (blog) say more than once: a given problem breaks down into smaller, simpler, often reusable parts. And now we’ve learned a word for that, boys and girls: Resolution!

Happy T-SQL Tuesday!
Jen McCown
http://www.MidnightDBA.com/Jen