Stupid SQL Tricks

Finished up a successful engagement as ETL/Data Warehouse developer for a set of Silverlight and iPhone marketing apps. Pretty cool, but less than impressed with the stored procedure support (read: "lack of") in MSFT's ADO.Net Entity Framework. Now I am back in the interview loops and am once again confronted with the bane of my existence, "Stupid SQL Tricks".

These are those crazy SQL problems that I have never actually used in thirty years of professional development experience, but that somebody found in the back of one of Joe Celko's books and thought would make a good interview questions. You know. The ones that involve a boat and a river...

Now I don't know about you, but I am a certifiable, left-brained creative type. Couldn't care less about the how many pinheads can dance on an angel or whatever. So in this post I am going to answer the last one I got, if for no other reason than being able to point the next right-brained analytical-retentive to my blog instead of having to answer the question at the white board.

This is the "what is the second from the top" question, usually worded something like: "You have a table containing names, dates, and amounts. Give me a list of the second most recent transaction by name." Yeah. Like anybody is ever going to actually USE that. So the right answer appears below. But if it gets you a job with a bunch of maniacs, don't blame me. I warned you.

At least, that's how I see it.


--
-- Stupid SQL Tricks
--
-- purpose:
-- select the Nth most-recent row
-- notes:
-- valid values of @n are 1-4
-- [tAmt] corresponds to ordinal position
-- history:
-- 20090918 james atwater created
--
begin

set nocount on

declare @n int;
set @n = 2;

declare @tbl table
( tblID integer not null identity(1,1) primary key
, uName nvarchar(160) not null -- user name
, tDate datetime not null -- transaction date
, tAmt decimal(10,2) not null -- transaction amount
) ;

insert @tbl
( uName , tDate , tAmt )
values ( 'James Joyce', '1/1/2001' , 4.00 )
, ( 'James Joyce', '1/3/2001' , 3.00 )
, ( 'James Joyce', '1/8/2001' , 1.00 )
, ( 'James Joyce', '1/6/2001' , 2.00 )
, ( 'Kathy White', '1/5/2001' , 2.00 )
, ( 'Kathy White', '1/4/2001' , 3.00 )
, ( 'Kathy White', '1/7/2001' , 1.00 )
, ( 'Kathy White', '1/2/2001' , 4.00 )
, ( 'David Green', '1/10/2001', 3.00 )
, ( 'David Green', '1/11/2001', 2.00 )
, ( 'David Green', '1/9/2001' , 4.00 )
, ( 'David Green', '1/12/2001', 1.00 )
;

select *
from @tbl t
where @n - 1 =
( select count(*)
from @tbl tx
where t.uName = tx.uName
and t.tDate < tx.date
) ;

First day, new company

Here begins my learning about life after Microsoft, phase two. Suspicions that the software industry's equivalent of a medical internship at a teaching hospital is to have done a stint at Microsoft. Either that or the equivalent of a stint at McDonald's to the hospitality industry. Met three people today who agreed that the reason they left after successful multi-year runs at the software giant was that they didn't like performance reviews. They were happy to be on the outside, but did not regret their time.

Microsoft has been very good to me. Learned from some great people, and confirmed my suspicions that I am better than most at my chosen speciality, which happens to be business intelligence. Am genuinely thrilled to know that the last eight years of my professional life have left me well equipped to move ahead, and only slightly the worse for wear and tear.

Here's hoping all my friends in and around Microsoft are happy and well this Christmas season. I have fond regards and greatest respect for one and all, including employees, contingent staff, partners and members of staffing firms alike. My earnest prayer is that you will weather the storms of whatever budgetary constraints may manifest over this next year or so as we collectively build our way out of America's current economic conditions.

Life is short, and milestones like the first day at a new company are too few to mark its labors. Family and friends are precious, and we make too little time for them as we strive to meet the demands of daily life. Do every kindness you can, and draw as close to those you love as possible. If nothing else, you can always start writing a blog.

At least, that's the way I see it.

Soft Skills

Or, "How I learned to stop hating office politics and started caring about people".

Read a post a while back saying the one thing most people dislike in their work lives is office politics. Understandable, to say the least. Alan Greenspan said his greatest mistake was to think banks will behave in their own best interest. Organizations sometimes do a poor job of communicating their collective best interest. Too, it often it seems that we lose track of our own personal interest, at least in the short term.

Those of us who instinctively rebel against all things political and aspire to some measure of intelligence find we can hide from it. We dive deep into whatever technical discipline gets us farthest from others and nearest to our one true love: ourselves. But ultimately technical prowess is worthless without technical peers. Of late I have caught myself fostering peer relationships with the earnest desire to see if other people regard "best practice" the same way I do. And not just to prove I am smarter (I know, it's heresy).

In my work as a business process analyst, this means helping folks align and enlighten their self interest to the collective interest of the enterprise. Of course we use cool tools like balanced scorecards and KPIs and dimensional data warehouses and such, but the point is always to connect people to strategy in a way that makes work life a little less daunting.

As I rotate the hat ninety degrees into the project manager role, it is no leap to connect the communication strategies to this same continuum of self interest. Project management must transcend boilerplate deliverables and take into account the main issue: To help people who are doing what they were made to do understand people who were made to do something else.

In the dev space, the connection can be more easily obfuscated by our natural bent toward details, working alone, late nights, and caffeine. Nonetheless true development (like Bertrand Russel's true mathmatics) has an evangelical component that can even transcends it's actual usefulness. Where usefulness and coolness collide we find the excitement and passion that, properly conveyed, can span the gap between an uber-geek and a marketing manager. By way of example, I submit the Sharepoint (MOSS) Business Data Catalog. Look it up.

So my thesis is that beyond all that is technically cool and wizardly, the relationships are where real value is added. And if this is my confession of defection from my engineering heritage, then so be it. Engineering and ingenuity have been too often seperated, especially when we consider "social ingenuity" as more than an oxymoron. I for one purpose to allow office politics to be the reminder that I need to use the gifts at my disposal to help people align their interests with those of the enterprise. It's fun, and the relationships formed are the only thing on this earth of real value.

At least, that's how I see it.

Homeowner's Association Blues

In twelve bars...

Sometimes I feel like the only person on the left coast who didn't buy more house than they could afford. Just the same, even the conservative path has it's pitfalls, and living in an older condominium means that, like as not, previous generations of board members left you holding some portion of the long-term maintenance "bag", so to speak.

In our case, the units were built before window flashing was required, back when aluminum windows were considered a good thing. Fast-forward a couple decades and we are left having to consider replacing them all or at least doing a lot of work to mitigate condensation, leaks, and other associated issues related to the edge of wetness upon which we reside here in the Pacific Northwest.

Thank God for Wayne Booth. Wayne has run a one-man contracting operation in Issaquah and Bellevue since he graduated from bible school at what used to be Lutheran Bible Institite. Wayne is about fifty, single, and easily the straightest arrow you will ever meet. He is frugal, thrifty, and downright close with a penny, even other people's! He is not, however, cheap.

Good thing, because anyone else would be seeing dollar signs and telling us to replace all the siding and the windows before even thinking about the paint job we have scheduled for next summer. And with thirty-six units across four buildings, it could run into some money.

So Wayne is crawling around and scheduling visits to each unit (no small task with everyone working all the time) and is planning out how best to approach a problem he can't really see. Did I mention Wayne has guts. Guts and thirty years of experience are a really good combination.

I wish everyone I work with in my professional life were half the man Wayne Booth is.

At least, that's how I see it.

Initialize

Job search is always fun. Helps the prayer life and reminds you of who your friends are. Freelance data geeks like me are almost perpetually in search of the next gig, putting us in league with professional musicians, used car dealers, and politicians.

As such, the act of digging up new clients serves as a gut check on our personal priorities, morals, and values as much as the usual introspections about our dedication to the craft and our personal convictions about one's value to society and the marketplace.

I just went through another one of those fun interview loops where a bunch of people with widely varying agendas and needs talk to you (ostensibly) about what they are looking for and deciding if your skills are a good fit. Often as not this has little if anything to do with the actual position description, as the real needs of the interviewers focus more on their personal career goals as the needs of the employer.

Most important thing to remember is that even when the news says unemplyment is up, I am only looking for one job, a job that not just anyone can do. Second most important is that failing to get hired, even after a successful interview loop, is not my fault. An earnest, honest resume and a willing, charitable heart are all any of us can bring to the table and if that is not enough then you would not want to work for them anyway. Of course, a recent trip to the dentist for a good tooth cleaning can never hurt...

At least that's the way I see it.