SQL Server Recursive query

Question

Some days ago I asked a question on SO regarding help on a recursive query.

The problem of that question was "How to get the history of a Person appointments".

Now I am having a problem similar to that one but it should answer to a slightly different question:

How to get an Appointment history?

For example if Appointment with ID = 5 has been postponed once and it was a postponement of another appointment how do I get the following result?

AppointmentID         PrevAppointmentID
-----------------    ----------------------
1                     NULL
5                     1
12                    5

Thanks for helping

Update:

These scripts will help to create the table for your trials

CREATE TABLE [dbo].[Appointments](
    [AppointmentID] [int] IDENTITY(1,1) NOT NULL,
    [IssueID] [int] NOT NULL,
    [Location] [varchar](255) NOT NULL,
    [Description] [varchar](255) NOT NULL,
    [AppointmentDate] [datetime] NOT NULL,
    [AppointmentHour] [datetime] NOT NULL,
    [Done] [bit] NOT NULL,
    [PrevAppointmentID] [int] NULL,
 CONSTRAINT [PK_Appointments] PRIMARY KEY CLUSTERED 
(
    [AppointmentID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Answer 1

Logic:

1. Get the appointment in question
2. Recurs joining to the parent application
3. Select all results

Query:

DECLARE @appointmentId INT
SET @appointmentId = 3

--
;
WITH  past
      AS ( SELECT   AppointmentId ,
                    PrevAppointmentId
           FROM     Appointments
           WHERE    AppointmentId = @AppointmentId
           UNION ALL
           SELECT   prev.AppointmentId ,
                    prev.PrevAppointmentId
           FROM     Appointments prev
                    INNER JOIN cte curr ON prev.AppointmentId = curr.PrevAppointmentId
         ),
    future
      AS ( SELECT   AppointmentId ,
                    PrevAppointmentId
           FROM     Appointments
           WHERE    AppointmentId = @AppointmentId
           UNION ALL
           SELECT   prev.AppointmentId ,
                    prev.PrevAppointmentId
           FROM     Appointments prev
                    INNER JOIN cte1 curr ON prev.PrevAppointmentId = curr.AppointmentId
         )
SELECT  *
FROM    past OPTION(MAXRECURSION 500)
UNION
SELECT  *
FROM    future OPTION(MAXRECURSION 500)

Answer 2

I didn't want to completely hijack his answer, so:

Using Brad's Answer

Here is the query to get the complete history:

WITH    cte
          AS ( SELECT   AppointmentId ,
                        PrevAppointmentId
               FROM     Appointments
               WHERE    AppointmentId = @AppointmentId
               UNION ALL
               SELECT   prev.AppointmentId ,
                        prev.PrevAppointmentId
               FROM     Appointments prev
                        INNER JOIN cte curr ON prev.AppointmentId = curr.PrevAppointmentId
             ),
        cte1
          AS ( SELECT   AppointmentId ,
                        PrevAppointmentId
               FROM     Appointments
               WHERE    AppointmentId = @AppointmentId
               UNION ALL
               SELECT   prev.AppointmentId ,
                        prev.PrevAppointmentId
               FROM     Appointments prev
                        INNER JOIN cte1 curr ON prev.PrevAppointmentId = curr.AppointmentId
             )
    SELECT  *
    FROM    cte
    UNION
    SELECT  *
    FROM    cte1

Answer 3

In response to a question from the OP:

Effective Dating

Effective-dating is when you add a DateTime column to the table that controls when the record becomes "effective". The column is then added to the PK of the table making each entry a record of what was "in-effect" at a given point in time (the effective date). With effective-dating, you never DELETE or UPDATE, only INSERT meaning that you always have a complete history of the object over time.

To find the most effective record, you select the row with the maximum effective that is not in the future

SELECT *
FROM   Appointments a1
WHERE  EffectiveDate = (SELECT MAX(EffectiveDate)
                        FROM   Appointments a2
                        WHERE  a1.AppointmentId = a2.AppointmentId
                               AND a2.EffectiveDate <= ISNULL(@asOfDate, GETDATE()
                       )

This means that you can also pre-date records. For example, you are approved for a pay raise today, but it won't go into effect for 2 weeks.

So to find the history of an appointment, you would simply:

SELECT *
FROM   Appointments
WHERE  AppointmentId = @appointmentId
ORDER BY EffectiveDate