Tag Archives: TSQL2sday

T-SQL Tuesday #016: Aggregates and Windowing Functions and Ranking! Yum!

T-SQL Tuesday #016Let’s get this out of the way right now: I am SO going to take advantage of this T-SQL Tuesday on aggregates to talk about OVER() and PARTITION BY. Aggregates and OVER go together like french fries and catsup…sure, you can have them apart, but together they’re just magical.

Note: If you’re not at all familiar with OVER (PARTITION BY) at all, take a quick look at my brief intro, Tip: Over and Partition By.

In my T-SQL Brush-up session (also known as the Forgotten SQL session), I walk my way through fairly random examples set in the AdventureWorks database, and miraculously end up somewhere sensible. 

(Tons more good talk and code after the jump…)

Continue reading T-SQL Tuesday #016: Aggregates and Windowing Functions and Ranking! Yum!

T-SQL Tuesday #014 Roundup

Wow…the inagural 2011 T-SQL Tuesday was so big, it lasted a week! A few quick observations, then on to the blog posts:

According to my terribly scientific observations of T-SQL Tuesday, the third most common techie resolution for 2011 is MCM prep!  The number two most common techie resolution is….Learning Powershell. As well it should be.    

The number one most common resolution is a near universal desire to give back more to the SQL community.  Roughly 90% who participated in this event resolved to blog more, speak more, help more on Twitter and other places, volunteer more…it’s astounding. I don’t suppose I should add much to this already 2,000+ word post, but I’d just like to say how deeply pleased I am that SO many of us are improving ourselves through what is, essentially, a communal charity of knowledge.  Cheers to you, Mr. and Ms. SQL Blogger. I salute you all!    

List of participants:    

  1. John Samson
  2. Rob Farley
  3. Noel McKinney 
  4. Pinal Dave
  5. Jason E Bacani
  6. Luke Hayler
  7. Mark Broadbent
  8. Aaron Bertrand
  9. Bradley Ball
  10. Nic Cain
  11. Cade Roux
  12. Steve Jones
  13. Nancy Hidy Wilson
  14. Jim McLeod
  15. Ken Johnson
  16. Gabriel Villa
  17. Andy Lohn
  18. Ricardo Leka
  19. Jason Strate
  20. Dev Nambi
  21. Erin Stellato
  22. Grant Fritchey
  23. Jen McCown
  24. Allen Kinsel
  25. Wes Brown
  26. Allen White
  27. Kendra Little
  28. Audrey Hammonds
  29. Julie Smith
  30. Dave’s SQL Blog
  31. Airborne Geek
  32. Pat Wright
  33. Bob Puserati
  34. Shannon Lowder
  35. Allan Hirt
  36. Michael J. Swart 
  37. Jason Brimhall 
  38. Paul G Hiles
  39. Matt Velic 
  40. John Welch
  41. Stacia Misner
  42. Stuart Ainsworth
  43. Lady Runay
  44. Thomas Rushton
  45. Gill Rowley
  46. Cameron Mergel
  47. John Racer
  48. Tjay Belt
  49. Samson Loo
  50. Paul Randall
  51. Oscar Zamora
  52. Kelly Martinez
  53. Kendal Van Dyke
  54. Tamera Clark
  55. Sean McCown
  56. Niko Neugebauer
  57. Rafael Salas
  58. SQLSuperman
  59. Tim Ford

Summary / comments on each blog post, after the jump:

Continue reading T-SQL Tuesday #014 Roundup

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….


“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.


“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?


“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