Incremental grouping row total in SQL Server 2000
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.... ID Contact_Dt ABC1 01/07/2012 ABC1 02/07/2012 ABC1 03/07/2012 ABC2 15/07/2012 ABC2 18/07/2012 ABC3 05/07/2012 ABC4 08/07/2012 ABC5 02/07/2012 ABC5 03/07/2012 ABC5 04/07/2012 ABC5 05/07/2012 I'd like to be able to add a sequence... ID Contact_Dt Seq ABC1 01/07/2012 1 ABC1 02/07/2012 2 ABC1 03/07/2012 3 ABC2 15/07/2012 1 ABC2 18/07/2012 2 ABC3 05/07/2012 1 ABC4 08/07/2012 1 ABC5 02/07/2012 1 ABC5 03/07/2012 2 ABC5 04/07/2012 3 ABC5 05/07/2012 4 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!!! Thank you
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][1] 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][2]. 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 [1]:
http://www.sqlservercentral.com/articles/T-SQL/68467/ [2]:
http://www.sqlservercentral.com/articles/T-SQL/61539/
@Pam I am glad I could help. Again, please use this script with caution, and try to find another way (best of which would be to upgrade to a newer version of SQL Server) if you ever experience performance issues with this one.
Meant to use comment
