Creating a dynamic ORDERBY in T-SQL

I'm in the midst of converting a Crystal Report into an Excel file output... While there is an increased benefit of more control over the output, however, I lose many features that Crystal has to offer. After weighting options, let's just say its much easier to create an Excel file for this particular report.

Anyway, in Crystal Reports, I had the luxury of dynamically sorting by various datatypes. Based on the sort type the user selected, it could be a combination of DateTimeStamp, ID, and UserName... in this case, we're talking about three different datatypes: datetime, int, and varchar respectfully. We may create a ORDER BY clause with DateTimeStamp ASC, ID DESC, UserName ASC or in whatever combination you want.

This is the minimum requirement of the sort options from the client application (all in ascending):

• DateTimeStamp, UserName
• UserName, ID, DateTimeStamp
• ID, DateTimeStamp

"No problem," I said, "Piece of cake." I can pass the @OrderBy parameter to a SQL stored procedure from the client and then we'll have the sorted data back to the client app as an Excel spreadsheet.

DECLARE @OrderBy int
SET @OrderBy = 1
SELECT 
    DateTimeStamp, ID, UserName 
FROM 
    [Table1]
ORDER BY
    CASE @OrderBy 
        WHEN 0 THEN DateTimeStamp ASC, UserName ASC 
        WHEN 1 THEN UserName ASC, ID ASC, UserName ASC 
        WHEN 2 THEN DateTimeStamp ASC, ID ASC 
    END

But the sort isn't perfect, something's always off, you know how "2" is always after "10" in Windows/SQL Server... something like that. Converts characters the same datatype as the first column, even tried convert(varchar(20), datetimestamp, 110), etc. It became clear that we cannot dynamically order columns with various datatypes like this! Yeap, it's always the little things. *sigh*

Looked it up online thinking there's a quick solution, and saw some people had suggestions here and there. So tried various queries like the ones in http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=5942 and none of them worked for me. T-SQL whines about having bad datatypes... Either of those solutions don't work (for me anyway) or they're wrong and not amply tested.

So here's how I was able to do it the neat and harmonious (read zen) way:

select
    datetimestamp, id, username
from 
    table1
order by 
    case @OrderBy 
        when 0 then datetimestamp ASC
        when 1 then username ASC
        when 2 then id
    end,
    case @OrderBy 
        when 0 then username
        when 1 then id
        when 2 then datetimestamp ASC
    end,
    case @OrderBy 
        when 1 then datetimestamp ASC
    end

Hope it is as enlightening for you as it was for me.

Want a great looking XML editor?

Use SQL Server Management Studio 2008!  That tool has an awesome XSL generator and has a button to nicely reformat mangled XML with indents and all.  Oh yeah, Visual Studio-style code coloring.

Satellite tugs

From http://www.space.com/missionlaunches/090218-dawn-asteroid-mars.html
But there is a downside to Dawn's swing past Mars. Just as the probe nabbed a speed boost from the planet, the encounter slowed Mars by a tiny fraction, mission managers said.  "The flyby will cause Mars to slow in its orbit enough that after one year, its position will be off by about the width of an atom. If you add that up, it will take about 180 million years for Mars to be out of position by one inch (2.5 cm)," Rayman said. "We appreciate Mars making that sacrifice so Dawn can conduct its exciting mission of discovery in the asteroid belt."

If one measley probe can tug at Mars, what about all those satellites around earth??  If earth's orbit shifts, the tides man, oh, the tides...

Words of the wise

Life isn't about finding yourself.
Life is about creating yourself.

Quotes unknown.

Finding code definitions in VS.NET IDE

How do I find the code its Interfaces copies?  I’m looking at a method however it also has an Interface.  I must use the Find dialog to search the entire solution? 

Go To Definition (F12), then it takes me to an interface. Is there a better way to find the functioning class rather than the Interface class?  I know tools like Resharper, etc makes it easier…

Wild idea to help the economy.

Let's take a company as an example: Bank of America.  They just went on an acquisition binge buying troubled outfits Countrywide Financial and Merrill Lynch.  BoA has been hoodwinked unfortunately cumulating with Thain’s decoration bill.  Anyway, as a result, Bank of America is announcing layoffs.

If the layoffs were from consolidating operations then there you go.  They're contributing to the rising unemployment rates, and while everyone was doing fine (ML, I think would have came out of their crisis ok, and Countrywide would have proportionally have less layoffs.)  Without those mergers, we would kept work “duplicated” however STILL EFFICIENT since whatever work they do is still for the betterment of their respective corporations.  Most respectable companies reduce staff out of attrition rather than outright laying them off.

Now, being taught economics, one thing always leads to another: corporation buys their weaker counterparts, lays people off due to duplication, then those unemployed would be collecting unemployment, further sapping companies out of money, then eventually those unemployed won't be able to buy shares, either outright or through their retirement plans, of any company simply because they cannot afford to. Corporation stock prices drops, lays more people off. Domino effect.

Remember when W. asked us to do the "patriotic thing" by spending money!  Well, shouldn't corporations start spending money to keep employees retained?

What goes around comes around...

Beautiful clouds

Driving on my way to work on C-470 north, I came across these gorgeous set of clouds! [Looking northwest towards the foothills.]

If the picture is bad, pardon me as it was taken by my BB Curve whizzing along at 65-70 mph!