3

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]
Improve this question
3
  • if design is something you can change, consider using effective dated rows Commented Oct 25, 2010 at 16:47
  • @Brad: what do you mean by "effective dated rows"? Commented Oct 25, 2010 at 17:05
  • it was a lengthy explanation, so I added another answer. Commented Oct 25, 2010 at 17:28

3 Answers 3

Reset to default
3

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
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

@Martin: It says that a maximum number of 100 recursion has been reached before completing the query...
@Lorenzo - that makes sense since it would keep going back and forward, back and forward ... update my answer, but still no clue if it works.
@Martin: cteAppointments when you do the inner joins is not defined. Do you mean respectively cteAppointments1 and 2? Sorry but I do know nothing about cte :(
@Lorenzo - this will get you the complete history, regardless if you put in 12, 5, or 1
@Martin: This is working. Million Thanks!!!! Without Brad initial work it would'nt be done so fast. +1 for giving credit to him!
3

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)
Improve this answer

14 Comments

@Brad: It return an error saying that "recursive reference are not admitted in subqueries" corresponding to the WHERE clause inside the EXISTS (WHERE prev.AppointmentId = curr.PrevAppointmentId)
If you post code or XML, please highlight those lines in the text editor and click on the "code" button (101 010) on the editor toolbar to nicely format and syntax highlight it!
This works, I just had to add edit the prev. into the second select
@Brad: now is giving another error. I have updated the question to include the create script for your trials
@Lorenzo - try the code now. It works in my little test environment I created.
|
2

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
Improve this answer

1 Comment

I knew the technique! But I never knew that I can refer to it with the name "Effective Dating". Thanks so much for the explanation

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.