I've had some interesting SQL feats to perform this week and i thought i would share.
At work, we've come up with a way show sales progress by "bowling" on the intranet. Each day we earn a score based upon what percent of the daily sales goal we achieved. Scores range from strike to gutter ball. We store a numerical representation of the score for each business day in a table. The problem was we wanted to be able to label certain days as "doubles" or "turkeys" if we got a series of strikes. This involved comparing a day's score with the two previous business days. I wanted a query that could grab those values. Table in our SQL Server looks like this: BowlingScore(dt_score datetime, score tinyint). The SQL i wrote will return a value for any given @SalesDate
SELECT
S1.score today_score, S1.dt_score today_date,
S2.score yesterday_score, S2.dt_score yesterday_date,
S3.score daybefore_score, S3.dt_score daybefore_date
FROM BowlingScore S1
JOIN BowlingScore S2 ON S2.dt_score=
(SELECT TOP 1 S2D.dt_score FROM BowlingScore S2D WHERE
dt_score<S1.dt_score ORDER BY S2D.dt_score DESC)
JOIN BowlingScore S3 ON S3.dt_score=
(SELECT TOP 1 S3D.dt_score FROM BowlingScore S3D WHERE
dt_score<S2.dt_score ORDER BY S3D.dt_score DESC)
WHERE S1.dt_score=@SalesDate
Another interesting challenge involved a list of users, a list of websites they can log into, and a record of each time they logged into any give site. Simplified versions of the respective tables are Users(user_id int, user_name varchar, customerlocation_id int); Sites(site_id int, site_name varchar); LogInLog(login_id int[auto], user_id, site_id, dt_login). I needed to grab a list of users from a particular customer location along with the last date they logged into a site as well as the name of that site. I came up with this:
SELECT USR.user_id USR.user_name, LIL.dt_login, SITE.site_name
FROM Users USR
LEFT OUTER JOIN LogInLog LIL ON LIL.login_id=
(SELECT TOP 1 login_id FROM LogInLog LOK
WHERE LOK.user_id=USR.user_id
ORDER BY dt_login DESC)
LEFT OUTER JOIN Sites SITE on SITE.site_id = LIL.site_id
WHERE customerlocation_id=@customerlocation_id
ORDER BY user_name
I had tried other variations, but will 3000+ users and a login log with hundreds of thousands of records it seemed to perform the best. I'm not sure what would have happened if i hadn't had the synthetic "login_id" key. The dt_login doesn't go into seconds so some users login to two sites within the same period (same dt_login value).
I'd be curious if others developed similar queries with different techniques.
Posted by Matthew at January 30, 2004 03:16 PM