x

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

more ▼

asked Aug 01, 2012 at 12:45 PM in Default

Pam gravatar image

Pam
10 1 1 2

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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

more ▼

answered Aug 01, 2012 at 02:47 PM

Oleg gravatar image

Oleg
15.9k 2 4 24

Thank you so much Oleg - yes it worked. You are brilliant!
Aug 01, 2012 at 02:54 PM Pam
@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.
Aug 01, 2012 at 03:00 PM Oleg
(comments are locked)
10|1200 characters needed characters left
Meant to use comment
more ▼

answered Aug 02, 2012 at 09:21 AM

Pam gravatar image

Pam
10 1 1 2

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x474
x8
x7

asked: Aug 01, 2012 at 12:45 PM

Seen: 956 times

Last Updated: Aug 02, 2012 at 09:27 AM