# Thursday, August 7, 2003

I have to admit that I am totally hooked on the The Rozenshtein Method. My buddy, Richard Campbell showed it to me a year or two ago and I have been hooked ever since. I recently demoed it at TechED in Dallas, a recent WebCast, VSLive in New York and will be showing it off at TechEd in Malaysia next week. I have gotten lots of email and positive feedback so I decided to blog it here.


Here is how it works. You need a crosstab query.  You have to move rows into columns. You also need ANSI 92 SQL that will run in any database.Well there are several ways to do this, but the most generic and one of the most powerful ways is called the Rozenshtein Method, which was developed by the Russian mathematician David Rozenshtein. This technique was taken from his book: Optimizing Transact-SQL : Advanced Programming Techniques.


First let’s look at the desired results. We want to take the orders data from Northwind and pivot the sales date (aggregated by month) as columns with the sum of the total sales in the row grouped by customer. It would look something like this:


CompanyName TotalAmount Jan Feb Mar…(etc)

Company1        100              25 33   10

Company2           467                 76 62    87



The TSQL query to do this is, go ahead and run it in Northwind in SQL Server:


SELECT  CompanyName, SUM((UnitPrice*Quantity)) As TotalAmt,

  SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-1)))) AS Jan,

  SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-2)))) AS Feb,

  SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-3)))) AS Mar,

  SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-4)))) AS Apr,

  SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-5)))) AS May,

  SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-6)))) AS Jun,

  SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-7)))) AS Jul,

  SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-8)))) AS Aug,

  SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-9)))) AS Sep,

  SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-10)))) AS Oct,

  SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-11)))) AS Nov,

  SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-12)))) AS Dec

FROM         Customers INNER JOIN

                      Orders ON Customers.CustomerID = Orders.CustomerID INNER JOIN

                      [Order Details] ON Orders.OrderID = [Order Details].OrderID

Group By Customers.CompanyName


So how does this work?


This method uses Boolean aggregates, so that each column has a numeric expression that resolves each row as a zero or one and that value (0 or 1) is multiplied by your numeric expression (Like TotalSales or (UnitPrice*Quantity). That is all there is to it, quite simple. But wait, there’s more to explain:


We want to create columns for each Month in our data. To find a month use DatePart. But we need to subtract the DatePart value (1-12) from the amount you’re looking for (1 for Jan, 2 for Feb, etc) as shown here for January:



So that true = zero, false > 0 or < 0. For example if the month you were looking for was January and the DatePart was 1 and you subtract 1 from that value you get 0, which is true. If you are looking for March you would get -2 and that would be false.


Next you have to compute the sign of the expression and get the absolute value like so:



This will give us a positive value. Remember 0 is still true. Now subtract the value computed from 1 in order to get a 0 or 1 from the value of your expression (the Boolean aggregate). The code is:



For example if you had March return 3 from the Datepart, 3-1=2 and 1-2 =-1. The absolute value is 1. This will always return 0 or 1. If your expression was zero, the value is now one. If was one, the value is zero.


Last step. Taking the SUM of the Boolean values will give you a count of the values that qualify. So you can find out how many sales you made in Jan, Feb, etc. So now multiply the value by the price and quantity, but remember its now one = true. Take a look here:


SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-1)))) AS Jan


If its zero, nothing gets added, if its one, you get the value of the sale. The sum of the total expression is the total of sales for the month. If you have a DatePart that is evaluated to 0 then ((UnitPrice*Quantity)*0) is 0 and those results are ignored in the SUM. If you have a month that matches your expression resolves to 1 and ((UnitPrice*Quantity)*1) is the value of the sale.


How easy!


But wait, there’s more! Suppose you wanted two values combined? Compute each value down to zero or one separately. Now you can use AND by multiplying, OR by adding (and reduce to 1 or 0 using SIGN).


Ok, have fun!!!

posted on Thursday, August 7, 2003 8:38:37 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [7] Trackback
# Wednesday, August 6, 2003

The Architect

I think everyone who wanted to see this movie has seen it and it is safe to post this now. I post this in honor of Jack, Kathleen, Jay and all the others last night that told me that they were still plugged into the Matrix:

The Architect - Hello, Neo.

Neo - Who are you?

The Architect - I am the Architect. I created the matrix. I've been waiting for you. You have many questions, and although the process has altered your consciousness, you remain irrevocably human. Ergo, some of my answers you will understand, and some of them you will not. Concordantly, while your first question may be the most pertinent, you may or may not realize it is
also irrelevant.

Neo - Why am I here?

The Architect - Your life is the sum of a remainder of an unbalanced
equation inherent to the programming of the matrix. You are the
eventuality of an anomaly, which despite my sincerest efforts I have been unable
to eliminate from what is otherwise a harmony of mathematical precision.
While it remains a burden to sedulously avoid it, it is not unexpected, and
thus not beyond a measure of control. Which has led you, inexorably, here.

Neo - You haven't answered my question.

The Architect - Quite right. Interesting. That was quicker than the

*The responses of the other Ones appear on the monitors: Others? What
others? How many? Answer me!'*

The Architect - The matrix is older than you know. I prefer counting
from the emergence of one integral anomaly to the emergence of the next, in
which case this is the sixth version.

*Again, the responses of the other Ones appear on the monitors: Five
versions? Three? I've been lied too. This is bullshit.*

Neo: There are only two possible explanations: either no one told me,
or no one knows.

The Architect - Precisely. As you are undoubtedly gathering, the
anomaly's systemic, creating fluctuations in even the most simplistic equations.

*Once again, the responses of the other Ones appear on the monitors:
You can't control me! Fuck you! I'm going to kill you! You can't make me
do anything!*

Neo - Choice. The problem is choice.

*The scene cuts to Trinity fighting an agent, and then back to the
Architects room*

The Architect - The first matrix I designed was quite naturally
perfect, it was a work of art, flawless, sublime. A triumph equaled only by its
monumental failure. The inevitability of its doom is as apparent to me
now as a consequence of the imperfection inherent in every human being,
thus I redesigned it based on your history to more accurately reflect the
varying grotesqueries of your nature. However, I was again frustrated by
failure. I have since come to understand that the answer eluded me because it
required a lesser mind, or perhaps a mind less bound by the parameters of
perfection. Thus, the answer was stumbled upon by another, an intuitive program,
initially created to investigate certain aspects of the human psyche.
If I am the father of the matrix, she would undoubtedly be its mother.

Neo - The Oracle.

The Architect - Please. As I was saying, she stumbled upon a solution
whereby nearly 99.9% of all test subjects accepted the program, as
long as they were given a choice, even if they were only aware of the choice
at a near unconscious level. While this answer functioned, it was obviously
fundamentally flawed, thus creating the otherwise contradictory
systemic anomaly, that if left unchecked might threaten the system itself.
Ergo, those that refused the program, while a minority, if unchecked, would
constitute an escalating probability of disaster.

Neo - This is about Zion.

The Architect - You are here because Zion is about to be destroyed.
Its every living inhabitant terminated, its entire existence eradicated.

Neo - Bullshit!

*The responses of the other Ones appear on the monitors: Bullshit!*

The Architect - Denial is the most predictable of all human responses.
But, rest assured, this will be the sixth time we have destroyed it, and we
have become exceedingly efficient at it.

*Scene cuts to Trinity fighting an agent, and then back to the

The Architect - The function of the One is now to return to the
source, allowing a temporary dissemination of the code you carry, reinserting
the prime program. After which you will be required to select from the
matrix 23 individuals, 16 female, 7 male, to rebuild Zion. Failure to comply
with this process will result in a cataclysmic system crash killing everyone
connected to the matrix, which coupled with the extermination of Zion will
ultimately result in the extinction of the entire human race.

Neo - You won't let it happen, you can't. You need human beings to

The Architect - There are levels of survival we are prepared to
accept. However, the relevant issue is whether or not you are ready to accept
the responsibility for the death of every human being in this world.

*The Architect presses a button on a pen that he is holding, and
images of people from all over the matrix appear on the monitors*

The Architect - It is interesting reading your reactions. Your five
predecessors were by design based on a similar predication, a
contingent affirmation that was meant to create a profound attachment to the rest
of your species, facilitating the function of the one. While the others
experienced this in a very general way, your experience is far more
specific. Vis-a-vis, love.

*Images of Trinity fighting the agent from Neos dream appear on the

Neo - Trinity.

The Architect - Apropos, she entered the matrix to save your life at
the cost of her own.

Neo - No!

The Architect - Which brings us at last to the moment of truth,
wherein the fundamental flaw is ultimately expressed, and the anomaly revealed as
both beginning, and end. There are two doors. The door to your right leads
to the source, and the salvation of Zion. The door to the left leads back to
the matrix, to her, and to the end of your species. As you adequately put,
the problem is choice. But we already know what you're going to do, don't
we? Already I can see the chain reaction, the chemical precursors that
signal the onset of emotion, designed specifically to overwhelm logic, and
reason. An emotion that is already blinding you from the simple, and obvious
truth: she is going to die, and there is nothing that you can do to stop it.

*Neo walks to the door on his left*

The Architect - Humph. Hope, it is the quintessential human delusion,
simultaneously the source of your greatest strength, and your greatest

Neo - If I were you, I would hope that we don't meet again.

The Architect - We won't.

posted on Wednesday, August 6, 2003 10:42:00 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [3] Trackback
# Tuesday, August 5, 2003

Yukon is in LA...

Up to my ears in coding and writing my MSDN Magazine article on Yukon. While NDAs are still in effect, I can let you know that Microsoft has made public the notion of writing a stored procedure in a CLR .NET Language. After hacking around most of the afternoon, I am marking this day down where I got my first CLR Stored Procedure working. Stay tunded to my MSDN Magazine feature story on Yukon that will be on the stands at the PDC.

posted on Tuesday, August 5, 2003 3:56:02 PM (Eastern Daylight Time, UTC-04:00)  #    Comments [16] Trackback
# Monday, August 4, 2003

New mail has arrived...

When I got home today, my brand new custom made Torelli Brianza racing bike arrived at my door. Nohting beats hand made Italian frames. It arrived just in the nick of time since I have a major tri this weekend. Components are a Campagnolo Veloce/Mirage mix. This is a steel frame, I will have this forever. I am in a state of bliss. Now off to the bike shop tomorrow to pick out some nice clipless pedals, a cycle computer and new shoes.

posted on Monday, August 4, 2003 10:45:33 PM (Eastern Daylight Time, UTC-04:00)  #    Comments [2] Trackback

Behind Every Great Man...

Is a great Editor. Just have to send some blog love to Melody Hendricks, my editor for the last 5 or 6 years. We have worked together on over a dozen conferences and maybe 50 magazine articles. Plus she puts up with all of my crap.

Thanks MelHen for making me sound smart in print. Want to edit my blog too???

posted on Monday, August 4, 2003 12:00:52 PM (Eastern Daylight Time, UTC-04:00)  #    Comments [2] Trackback
# Sunday, August 3, 2003

Ready for Mount Everest....

A nice climb up Mt. Rainer (14,410'), this weekend via Paradise and Camp Muir. We are all systems go for Mt. Everest in a few weeks...The altitude was no problem.

Over 7,200' was all snow. And a lot of it. Some cool snowboarder chicks hiked all the way up to Muir at 10,000' to then went on down, I have to go back and do that.

Kevin and I ran up the hill. We covered the run from the snow line at 7,200' to Muir at 10,000' in about 3 hours. (Coming down was less than an hour, virtual skiing.)

I am now publically giving some credit to my running partner Kathleen. She meets me 3 days a week, rain or shine (and it seems to mostly rain), hung-over or not (even if I was the cause of her hangover the night before), and likes to run fast. She also motivates me or rather likes to yell at me when I am dogging it saying things like "How are you going to get up Everest if you can't make it around the loop!" Thanks, I owe you one..

Photos up soon...

(WiFi Internet Access in the Airport rocks!)


posted on Sunday, August 3, 2003 1:51:46 PM (Eastern Daylight Time, UTC-04:00)  #    Comments [17] Trackback
# Friday, August 1, 2003

Preparing for Mount Everest


I am sitting at JFK airport waiting for my flight to Seattle. I have flown into Seattle at least 50 times since 1996 and each and every time I have went to Seattle in my life was to go do work or attend a meeting at Microsoft up in Redmond. Today is something new for me. I am going to Seattle not for Microsoft but for personal reasons. Ok, ok, I am going to drop by campus anyway and do lunch with the “RD Mom”, Eileen Crain, the PM for the Regional Director Program. (lunch doesn’t count) And after spending the week in New York at VSLive! with RDs Andrew Brust, Billy Hollis, Jackie Goldstein, Juval Lowy, and Ken Getz, this does feel like being summoned to the Mother Ship-but no, my visit to campus is completely voluntary. I am in Seattle for personal reasons.


In 29 days, I arrive in Kathmandu, Nepal to begin my trek to Mt. Everest with my pal Kevin Collins (who is the PM for SQL Server CE). Kevin and I are gong to do a high-altitude training run this weekend on Mt. Rainer (14,410'), outside of Seattle. Mt. Rainer is the second tallest mountain (by 54 feet!) in the lower 48. Ok, technically it is a Volcano (and an active one that is!). So it is the tallest Volcano in the continental United States! 


If you don’t hear from me soon, you know where to look for my body! No, don’t worry, while a few people die on Mt. Rainer each year, Joel Check (old Jet database engine Dev who is climbing with Kevin and I this weekend) and I have a tremendous amount of high altitude experience-and know our limits. Also, I can’t die, I have to be home for my teammate Tom Halligan’s birthday celebration on Tuesday. That is going to be a night of sin…

posted on Friday, August 1, 2003 4:57:01 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [3] Trackback
# Thursday, July 31, 2003
Visual Studio code name Whidbey
posted on Thursday, July 31, 2003 7:28:39 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [18] Trackback
VSLive Workshop
posted on Thursday, July 31, 2003 7:11:53 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [23] Trackback
# Wednesday, July 30, 2003

No More Radio...

das Blog is live! Thanks to Clemens for all his help...

I installed from source, took about 20 minutes of actual time to install locally, import my old entries from Radio, deploy to my ISP, etc. (Real time was a few hours since I was doing this in-between my VSLive sessions and had to get the ISP to set up the correct permissions, install 1.1, etc) I got it working first on 1.0  at the ISP and then moved it to a box running 1.1: much better..Gonna start the redirect from Radio today.

Now that I was the uber tester, I want to help Clemens somewhat in all of this. I have some ideas on improving the cache using the cache API w/ vary by param, gonna work with Clemens on that one.

Oh yea, comments work!!

posted on Wednesday, July 30, 2003 5:32:32 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [19] Trackback