What is Will Thinking: 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!