# Wednesday, February 23, 2005

I am sitting at an Internet Cafe in Ushuaia, Argentina, the world´s southern most city getting ready for the marathon in Antarctica on Saturday. Linda says that after running for 5 days in Buenos Aires in 90 degree heat running in the snow and ice is welcome! Will be offline for 2 weeks, see you on the other side...

posted on Wednesday, February 23, 2005 9:57:34 AM (Eastern Standard Time, UTC-05:00)  #    Comments [14] Trackback
# Monday, February 21, 2005
BA is a fun city and very hot to train (run and bike). About 90F here. Photos to come as soon as possible.
posted on Monday, February 21, 2005 2:15:04 PM (Eastern Standard Time, UTC-05:00)  #    Comments [10] Trackback
# Thursday, February 17, 2005
See you on the other side!
posted on Thursday, February 17, 2005 6:24:44 PM (Eastern Standard Time, UTC-05:00)  #    Comments [21] Trackback
# Wednesday, February 16, 2005

Bill Gates yesterday announced that Microsoft will ship an update to Internet Explorer, IE 7. It will focus on Security and Features and only run on Windows XP SP 2. In addition, IE 7 will include Microsoft’s AntiSpyware technology for free.

 

This is all good. FireFox has been gaining market share since it is newer, more secure, and has better features; I was dreading waiting until Longhorn to have a new version of IE.

 

Looking forward to installing it...

posted on Wednesday, February 16, 2005 2:47:13 PM (Eastern Standard Time, UTC-05:00)  #    Comments [10] Trackback
# Monday, February 14, 2005

I know this. (Ask me if I care.) Anyway, all of those folks who dislike me usually say "is there a way to shut him up." Well there is a way!!!! Have me train for a marathon. Ran 18 miles on Saturday and then a 9.3 mile race on Sunday inside the “gates“ in Central Park. I was very quiet all weekend. (Marathon in 12 days)

 

Another thing people thought was impossible was referencing a RANK()ing function in a WHERE clause in SQL Server 2005 or using an aggregate with the Ranking and Windowing functions. A simple solution that I did not see in any of the docs, use a Common Table Expression. Take this simple aggregate, rounding to the nearest 100th the sales figure from AdventureWorks;

 

Select CustomerID,round(convert(int, sum(totaldue))/100,8) *100 as totalamt

From Sales.SalesOrderHeader

Group by CustomerID

 

Gives you results like:

 

CustomerID  totalamt

----------- -----------

22814       0

11407       0

28387       600

15675       7900

18546       0

 

(and so on)

 

What if you want to rank them? Easy, make the aggregate a CTE and rank over the new field:

--rank by totaldue, summed and rounded (nearest 100)

--need a CTE to do a sum & rounding

--so this example will have a

--customerID summed with all of

--their orders

With CustomerSum

As

(

Select CustomerID,round(convert(int, sum(totaldue))/100,8) *100 as totalamt

From Sales.SalesOrderHeader

Group by CustomerID

)

Select *,

            Rank() Over (Order By totalamt Desc) as Rank

From CustomerSum

 

Results:

CustomerID  totalamt    Rank

----------- ----------- --------------------

678         1179800     1

697         1179400     2

170         1134700     3

328         1084400     4

514         1074100     5

(and so on)

 

Ditto if you want to filter a query by the results of a ranking function. Just move the ranking function up to the CTE:

 

--use a common table expression if you want

--to filter by one of the rows that contain a

--ranking function since ranking functions

--are not allowed in where or having clauses

With NumberRows

As

(

Select SalesOrderID, CustomerID,

            Row_Number() Over (Order By SalesOrderID) as RowNumber

From Sales.SalesOrderHeader

)

 

Select * from NumberRows

where RowNumber between 100 and 200

Order By SalesOrderID

 

Resutls:

SalesOrderID CustomerID  RowNumber

------------ ----------- --------------------

43758        27646       100

43759        13257       101

43760        16352       102

43761        16493       103

43762        27578       104

(and so on)

 

 

 

 

posted on Monday, February 14, 2005 5:49:05 PM (Eastern Standard Time, UTC-05:00)  #    Comments [4] Trackback
# Friday, February 11, 2005
wow.
posted on Friday, February 11, 2005 2:32:54 PM (Eastern Standard Time, UTC-05:00)  #    Comments [30] Trackback
# Monday, February 07, 2005

I made the rounds Friday night on .NET Rocks. Chatted about Corzen, Yukon, RAID, the .NET Auction and of course my crazy adventures. You can listen here.

posted on Monday, February 07, 2005 12:22:34 PM (Eastern Standard Time, UTC-05:00)  #    Comments [11] Trackback
# Friday, February 04, 2005
Tonight I go live on .NET ROCKS (http://www.dotnetrocks.com/), my second interview and their 99th show! We are scheduled to talk about: SQL Server 2000 Interop SQL Server 2005 XQuery and other cool features .NET Charity Auction on eBay My upcoming marathon in Antarctica
posted on Friday, February 04, 2005 10:12:11 AM (Eastern Standard Time, UTC-05:00)  #    Comments [10] Trackback
# Wednesday, February 02, 2005
The winners for the The .NET Celebrity Auction for Aceh Aid at IDEP are set. The offical ebay price was $152.5, but several bidders bid higher, so we stand to earn $6000 for the charity!
posted on Wednesday, February 02, 2005 9:07:05 AM (Eastern Standard Time, UTC-05:00)  #    Comments [12] Trackback