SQL Server recursive query

Question

I am new to SQL Server development. Most of my experience has been done with Oracle.

suppose I have the following table that contains Appointments objects

CREATE TABLE [dbo].[Appointments](
    [AppointmentID] [int] IDENTITY(1,1) NOT NULL,
    .......
    [AppointmentDate] [datetime] NOT NULL,
    [PersonID] [int] NOT NULL,
    [PrevAppointmentID] [int] NULL,
 CONSTRAINT [PK_Appointments] PRIMARY KEY CLUSTERED ([AppointmentID] ASC)

An appointment can be postponed so, when this happens, a new row is created on the table with the PrevAppointmentID field containing the ID of the original Appointment.

I would like to make a query to obtain the history of a Person appointments. For example, if the appoinment with ID = 1 is postponed two times, and these postponements have created appointments with ID = 7 and ID = 12 for the same PersonID, I would like to make a query that returns the following results:

AppointmentID         PrevAppointmentID
-----------------    ----------------------
1                     NULL
7                     1
12                    7

If using Oracle I remember that something like this can be obtained using the CONNECT BY PRIOR clause.

Is there any way to make a query to achieve these results?

I am using SQL Server 2005/2008.

thanks in advance

SQL Server recursive query

1 Answers