Incremental grouping row total in SQL Server 2000

There is a way to do it, but it is kinda nasty, and might lead to performance problems if you have 100K + records in your table. I will still provide a script at the end of this answer though I don't like the solution.

Windowing functions which were introduced in SQL Server 2005 make these types of queries trivial, but if you still have to use SQL Server 2000 then, sadly, there is are no good soluitions. I remember from those days that the only single thing I wished existed in SQL Server 2000 was some sort of equivalent to the Oracle's rownum, which was available there out of the box. Everything else in SQL Server 2000 was better, but the lack of this rownum equivalent made the queries to calculate the running totals and counts pretty complex. Please read the excellent article titled Solving the Running Total and Ordinal Rank Problems written by @Jeff Moden, it is a must read if you want to completely appreciate the challenges associated with this problem.

If you had SQL Server 2005 then you would simply use something like this query:

 select 
     ID, Contact_Dt, 
     row_number() over(partition by ID order by Contact_Dt) Seq
     from dbo.Appointments;

But if you have to use SQL Server 2000 then you can (at your own risk) try the infamous triangular join solution. The triangular joins are best explained, again, by @Jeff Moden in his article titled Hidden RBAR: Triangular Joins. Interestingly enough, the triangular join solution is what used to be recommended by some old MSDN blogs back in the day. Here is the query you can try:

 select
     ID, Contact_Dt, 
     Seq = 
     (
         select count(1) 
             from dbo.Appointments 
             where ID = a.ID and Contact_Dt <= a.Contact_Dt 
     )
     from dbo.Appointments a

The above should work in SQL Server 2000. I know that I might get ridiculed for this one, but I am just trying to help. Please use the above statement at your own risk because it will lead to some performance issues if you have a huge table to query.

Hope this helps,

Oleg

Meant to use comment