What is Will Thinking

2006/10/30

Fun with SQL derived tables

In the world of querying databases, every now and again you come across something that makes you say "Holy cow! That's pretty cool!".

That very thing happened to me a few days ago while doing some SQL cowboy stuff for a report here at work. Here's how it went down:

Thanks to some folks who may not have been the best at data storage and database design, we have a table that's almost impossible to get useful information from. Coincidentally, this is the table that holds some of the most important data in our enterprise. Naturally, a user requested a report on data from this table. Nasty self-joins and some really cryptic field names are pretty much the norm in this table, as are datatypes that make you scratch your head. I'll simplify most of this stuff for the sake of readability, but this also was the reason for not doing a few of the more well-known solutions.

I managed to data scrub my way down to only having two records for each REQNO, and I created a view from my result set.
Here was my source view's layout:

REQNO int,
transaction_code int,
milestone_date datetime

I needed to get all columns in the view, the date of first milestone, the date of the second milestone, and the difference between those two dates. I was faced with a pretty ugly GROUP BY - HAVING query, a nasty self-join query, or a new option suggested by a colleague, which was using derived tables.

Derived tables work like a cross between a view, a temp table, and a correlated subquery. Instead of selecting data from a table or a view, you simply replace the FROM clause with another query.

In my case, I needed to join data with the first transaction code to data with the second transaction code and get the amount of days between the two dates associated with those transaction codes.

Here's the query (including the derived tables and date arithmetic) that I used to get what I needed:

select
a.reqno, a.transaction_code, b.transaction_code, a.milestone_date as 'BeginDate', b.milestone_date as 'EndDate', datediff(day, a.milestone_date, b.milestone_date) as 'DaysOpen'

from (select * from mydata_vw where transaction_code = '0201') as a,
(select * from mydata_vw where transaction_code <> '0201') as b

where a.reqno = b.reqno

Pretty cool, huh? I'm not sure I would want to put a query like this into production because it was a fairly poor performer, but it got me the data I needed and magically turned my data into useful information. Might be something to think about the next time you're dealing with data that seems to require a big nasty query.


Share this post!

del.icio.us digg reddit Yahoo MyWeb NewsVine Furl



This post is tagged , , , , , and

2006/10/26

A decent pair of pants!

I must be on a Superfriends kick here lately, because I ran across another clip on Youtube that made me laugh.

This one's an old Cartoon Network commercial that ran a few years back. Made me laugh back then, and I'm pretty happy to have found it again. If I could only find those "Lasso of Truth" commercials they used to run, then I'd be set on my Superfriends fix.

Not only do I like the dramatic climax of the clip, but I especially like the line "Scarecrow - you're...you're...you're...made of straw! What more could you want?"

Enjoy!



Share this post!

del.icio.us digg reddit Yahoo MyWeb NewsVine

This post is tagged , , , , , and

2006/10/25

At least Batman has deductive reasoning

The following YouTube clip led to a pretty funny email exchange with my friend Zach this morning. Here's the clip, and the email exchange is down below that.



The email exchange:

[zach]:
BOOKMARKED!!! oh man, that was awesome. one of the best word combos in that movie "@ss clown." i have to start using that more.

[me]
That was great, wasn’t it?

[zach]:
yeah, especially to use the super friends. what an hilarious combo... office space and super friends. i can totally see the super friends in dead-end office jobs.

[me]
Yup, worried about being downsized all the time. Especially Batman. Surely someone’s gonna come along who has all those cool toys and stuff, but works cheaper than Batman. The other guys have genetic powers, but Batman just has a bunch of cool toys that Alfred invented. I mean, couldn’t he logically be replaced by a well trained Inspector Gadget?

[zach]:
hahahaha!! you're thinking about this waaaay too much. but while we're on it, i think robin is in more danger than batman. he doesn't have anything except he works with batman. at least batman has the intellect and deductive reasoning. but i guess inspector gadget had that too. well, not really. he just bumbled into the solutions.

i think our work life would be better if more girls wore wonder woman outfits... but knowing our luck, the wrong women would wear that.

[me]
Robin is definitely in trouble. Frankly, I don’t know how he’s managed to avoid a sexual harassment lawsuit with those shorts he wears all the time.

I think about this waaaaaay too much.

[zach]:
AHAHHAAHHAHA!!!! you should totally write all this up on your blog.

Damn it feels good to be a gangster.

Share this post!

del.icio.us digg reddit Yahoo MyWeb NewsVine

This post is tagged , , , , , and just for the hell of it,

2006/10/24

Kevin Tillman has something to say

And it ain't too good if you happen to be one of the folks in charge of the United States.

Normally I'm not a big fan of October and November in even-numbered years because you can't get away from assholes trading snide comments about each other. It happens on every other commercial on TV. Every day when I go get my mail, I have 10 new glossy pieces of junk mail bitching about how this guy is wrong and this other guy is right. Of course, each one in the stack contradicts the one before it. Gets pretty muddy as to who stands for what, and what difference either one of them will even make in the end.

I think sometimes this is how elections end up coming down to the extreme sides of either party, neither of which speak for most people out there. I also think that maybe that's what politicians have planned, and maybe that's why they get this way at the end of October. Maybe they figure that they are best served by the apathy they generate in the majority of most Americans.

Anyway, Kevin Tillman is Pat Tillman's brother. Pat Tillman was the guy who left the NFL to join the Army Special Rangers and serve with his brother, only to be killed by friendly fire in Afghanistan. That was subsequently covered up by the Army, because it indeed looks pretty bad that someone as selfless as Pat Tillman was killed so stupidly. Even more stupidly than normal. Kevin Tillman was discharged from the Army in the summer of 2005 and has written a document that's a must-read, unless you're one of the group of people in charge of the country right now.

There are so many people out there right now who have not earned the right to speak about the war and the way things have unfolded over the past few years. Kevin Tillman has more than earned his right to speak.

Here's the link...

Suggest to del.icio.us

Digg me

This post is tagged , , and

2006/10/17

A helicopter outside my window?

There's a helicopter hovering outside my window at work as I type this. This sort of thing wouldn't be too unusual if I worked in a high rise building, but this one only has two stories.

Doesn't look to be a LifeFlight helicopter or a military one. Doesn't really have much in the way of official markings. It's black or dark brown with a gold A-Team like stripe.

It's been out there for about 30 minutes now. Also kind of funny that we're in the approach path for Nashville International Airport. Those planes are taking off/landing pretty close by, so the planes are pretty low. Great neighborhood my office is in, huh?

Hmmm...

Anyway, I took some pics.














This post is tagged , , and

2006/10/13

The Redneck Robber is George Lucas!

Most of us here in middle TN have heard about the redneck robber by now. He's some guy who's been robbing banks all year and dresses like a redneck. Since this is the southeastern United States, you can imagine that they haven't had much success catching this guy. I mean, I can point to five guys who look and dress like this guy, and I'm at work. In an office park.

On the Fark.com thread about this story, user sandm000 pointed out that the guy sure looks an awful lot like George Lucas. Maybe too much. You be the judge:

Redneck robber:


George Lucas:


and



Maybe George Lucas is low on cash. I mean, there has to be a reason he keeps putting out all those versions of the Star Wars movies, right? Here's the FBI's Wanted page for the Redneck Robber.

Suggest to del.icio.us

Digg me

This post is tagged , , and

2006/10/10

Reasons why it sucks to be in San Diego

San Diego used to be one of my favorite places on earth.

San Diego is where I was (happily) stationed for 4.5 years. I lived there as a kid. The weather's awesome. The food's great. The people are pretty laid back, and, for the most part, cool with one another. There's an ocean there, complete with pretty decent waves and beautiful scenery. After years of being doormats, the Chargers are looking pretty good. The Padres have had a good past few seasons (if for some incomprehensible reason you can stomach watching baseball). Plus, if you're being chased by the cops, it's relatively easy to duck into Mexico.

Unfortunately, the place is being run into the ground. I have no idea why this is happening, but the city government there seems determined to bankrupt the place. This has been slowly happening for the past few years, but's getting really blatant now.

Here's the latest, as quoted from the San Diego Union-Tribune:

The San Diego Workforce Partnership spent $2.6 million on an online program for job hunters that took four years to develop and is little more than a compilation of Web links that so far has attracted 199 users.

Funded with money from a $28.6 million grant awarded to the Partnership in 2001 by the U.S. Department of Labor, the online jobs program was proposed as an interactive Web portal that would offer job training and resources to military personnel heading into the civilian work force.


OK, $2.6 million didn't sound too awfully bad for a web site dedicated to helping people get jobs. That 199 users figure seems pretty crappy, though. Wait a minute, it took four years to build this site? Must be a pretty good site. Check it out, then come back to read the rest. I'll be here.

http://www.sandiegoatwork.com/

Wait a fecking minute, that site cost $2.6 million and took 4 years? And why did they name it "Sandie Goat Work"? Why did it take me 5 seconds to load the first page with Firefox, a 1 year old laptop, and a broadband connection to the internet?

When I clicked on the "Job Seekers" section, I got links to "Career Centers", "Employment and Training Informational Kiosks" , something called "Project Earn", "Individuals with Disabilities Job Searching Assistance", something called "Cabrillo", and links to Monster and HotJobs?

I'm pretty sure I could have had this site up in an afternoon for about three hundred bucks.

Here are more reasons (in no particular order) why it sucks to be living in San Diego.


  • House prices are about to fall through the floor.

    Real estate speculation became so rampant in San Diego that the median home price just fell below half a million dollars. Mortgage-calc.com tells me that a 30 year mortgage for $500K is gonna cost me almost $3000 a month. Who exactly can afford that? None of the cops or teachers I've known have that much money to spend on a house payment every month. Keep in mind, that's median price.


  • Consulting fees to figure out what the problems are has cost the city over $30 million.

    Um, I think they might have put a finger on the problem, but I don't think that report's going to have "quit spending money on consultants" on there.


  • Policemen are taking demotions and leaving to go work in other towns.

    Do you think police in Alexandria, VA make more than police in Washington, DC? Me neither. Even in the Sim City games it's a bad idea to underpay cops.


  • The airport is tiny and overcrowded, yet no one can figure out where to build a new one. Since the housing boom (see above), there's not exactly a lot of space to build a new airport to replace Lindbergh Field, which is so cramped and crowded and far beyond it's capacity that it's almost less of a pain in the rear to drive from Nashville to San Diego than it is to fly.

    After another one of those gazillion year long studies costing who-knows-how-much, there are two plans. 1.) Take over or share useage with a military installation or 2.) Build an airport in another county that's two a two hour drive over a 4100 foot mountain range.

    The military nixed option 1 (duh) due to preparedness concerns and overall stupidity. Option 2 seems the only thing left now, but there are a few more problems. Since it would most likely take over two hours to drive from San Diego to the proposed new airport, build a "maglev train" at an estimated cost of roughly $10.6 billion. That's right, (pinky finger to corner of mouth)10.6 BILLION DOLLARS! That's just to get to the airport! Plus, since the site studied conflicts with both military and Mexican airspace, a proper approach would send jets almost all the way to Phoenix. Throw in an endangered lizard species, and this seems like kind of a bad idea also.

    Here's a small graphic to represent just how far away this airport site would be from San Diego...



  • The Mount Soledad cross lawsuit. This lawsuit, seemingly a fairly blatant violation of separation of church and state, has been costing San Diego in legal bills since 1989. Just recently, the land that the cross (or war memorial, depending on how much of a revisionist historian you are) sits on has been transferred to the federal government. As a citizen, this argument is debatable. As a taxpayer however, this should have been solved years ago, and for a lot less money.


  • Finally, we have the whole Chargers ticket fiasco. I can't find a link for it since it's been renegotiated, but here was the jist: Basically, the Chargers were tired of not selling out their stadium. Instead of making moves toward not sucking quite so badly, they cut a deal that made the games blacked out on local television if the game wasn't sold out. In their infinite wisdom, the San Diego city government decided to buy up all remaining tickets so the games would always be shown on television. I have no idea how much this cost San Diego, but ticket prices start at $79 apiece. I can't imagine the city was getting much of a break on this (now renegotiated) deal.

    Since the Chargers are kind of pricks to their fans in general, I figured I'd bring up a side point: you are often forced to buy tickets to a less popular game in order to see a more popular game. For example, if you wanted to go see the Chargers play the Broncos (should be a very popular game, give the respective teams' amounts of success this year) in December, you'll also have to buy a ticket to see another less popular game. What a bunch of assholes, man.




That's it! I guess I'm done ranting about a city I don't even live in anymore. I feel pretty bad about my friends and family that do still live there, though.

Share this post!

del.icio.us digg reddit Yahoo MyWeb NewsVine

This post is tagged , , , , , , , and