# Tuesday, March 29, 2005

 Using Ranking and Windowing Functions in SQL Server 2005. See it here.

posted on Tuesday, March 29, 2005 10:45:56 AM (Eastern Standard Time, UTC-05:00)  #    Comments [0] Trackback
# Wednesday, March 16, 2005

A long time ago Microsoft said that it is orphaning Visual Basic 6.0 on March 31st, 2005. That is in 2 weeks. So basically you will no longer be able to buy it or get free technical support (as well as any bug fixes to the product.) In essence, the warranty is expired. Microsoft will continue to support VB 6 on a fee basis until March 31st, 2008.

 

This is ok since VB 6 RTMed in the summer of 1998 and has been superseded by its two predecessors: Visual Basic .NET 2002 and Visual Basic .NET 2003. It has been almost 7 years and two upgrades, so this should not be an issue, right?  

 

Well, over 100 Microsoft MVPs (Most Valuable Professionals) have signed an online petition that demands Microsoft resume development and support VB6. The MVPs are calling for all VB developers and IT leaders to review and consider signing the petition.

 

Can 100 MVPs Be Wrong?

 

YES.

 

All good things come to an end. VB 6 is old technology and it is time to move on to the more powerful and flexible VB .NET or a more modern language like C# or Java. We always knew this day would come. Nobody uses WordStar for DOS anymore-for a reason.

 

I build tons of apps with VB6 back in the day. They will continue to run until the end of time, upgraded or not-MS has not dropped support for the VB6 runtime, which is actually part of Windows.

 

I do understand that some companies and government organizations are slow to upgrade and that upgrading can be expensive at times. But that said, the writing has been on the wall since the PDC in 2001. Microsoft made the orphan announcement almost a year ago.

 

VB6 developers say things like “VB .NET is too hard” or “it is difficult to upgrade.” Nonsense. I am by no means a genius and I was able to learn .NET when it shipped and was able to upgrade all of my stuff. In addition, I found that .NET was *easier* to work with and implement (epically ASP .NET)!!! My staff of 10 developers at Zagat.com was able to make the switch pretty fast.

 

I am still a VB MVP. I have, however, completely made the switch to C#. December 2002 was when I defected. That said, I still have a soft spot for VB and hope to see everyone migrate as soon as possible. You have four years, so get cracking. Maybe by March 31, 2008, VB .NET 2002 will be unsupported. J

 

 

posted on Wednesday, March 16, 2005 12:26:19 PM (Eastern Standard Time, UTC-05:00)  #    Comments [5] Trackback
# Monday, March 7, 2005

It took me 33 years but I have finally achieved my life’s goal of walking barefoot on all 7 continents. It happened twice actually, before and after the marathon while changing my shoes. So how was it many have asked?

 

Antarctica is a great place since basically there are no humans (save for a few bases doing scientific research). The marathon was more like an eco-challenge. Did a time of 6:22:48, sure that sounds slow but the winner did 4 hours and he usually does a 2:15 marathon. Basically lots of mud, snow, ice, rocks, hills and then a GLAICER! Yes a glacier we had to climb up for over a mile and a half. Then down. Then again! Got my sneaker pulled off my mud and such but it was so much fun! Finished I think 113 out of 180 (200 started), Linda did about 50 minutes faster and was in the top 60! Awesome!!!

 

In one day two life goals accomplished, visiting the 7th continent and running my first marathon. I also peed on all 7 continents and made a cell phone call on all 7 continents (the Russian base on King George Island had a tower, go figure.) I maybe the first to have done that!

 

Well back and better than ever. Techie posts resume tomorrow.

posted on Monday, March 7, 2005 1:59:57 PM (Eastern Standard Time, UTC-05:00)  #    Comments [23] Trackback
# 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