I'm a basic user of SQL Server 2000 and have been searching (for several weeks now!) for a way to produce a grouped incremental row total of contact dates by ID. Each ID is unique but there are thousands of them and can have more than one date.
My simple table called Appoinments shows something like....
I'd like to be able to add a sequence...
ID Contact_Dt Seq
Basically the count starts again at each change in ID. I need to be able to do this so that I can obtain the third contact date made with each ID.
The format of the ID is nvarchar (20) and the contact_dt is smalldatetime.
I know similar things have been posted before but I can't get my head round them as I don't have the experience trying to decifer the complex code using other people's references. The most I do is simple stored procedures.
Please help - this is driving me mad!!!
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:
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:
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,