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]

if design is something you can change, consider using effective dated rows — Brad, Oct 25 '10 at 16:47

Martin · Accepted Answer · 2010-10-25T17:39:16.440

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

edited Oct 25 '10 at 17:39

answered Oct 25 '10 at 16:47

Martin

11,031
8
50
77

@Martin: It says that a maximum number of 100 recursion has been reached before completing the query... – Lorenzo Oct 25 '10 at 17:01
@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 Oct 25 '10 at 17:10
@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 Oct 25 '10 at 17:15
@Lorenzo - this will get you the complete history, regardless if you put in 12, 5, or 1 – Martin Oct 25 '10 at 17:39
@Martin: This is working. Million Thanks!!!! Without Brad initial work it would'nt be done so fast. +1 for giving credit to him! – Lorenzo Oct 25 '10 at 17:47

Brad · Answer 2 · 2010-10-26T12:50:51.867

3

Logic:

Get the appointment in question
Recurs joining to the parent application
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)

edited Oct 26 '10 at 12:50

answered Oct 25 '10 at 16:52

Brad

15,361
6
36
57

@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`) – Lorenzo Oct 25 '10 at 17:03
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! – marc_s Oct 25 '10 at 17:18
This works, I just had to add edit the `prev.` into the second select – Martin Oct 25 '10 at 17:20
@Brad: now is giving another error. I have updated the question to include the create script for your trials – Lorenzo Oct 25 '10 at 17:21
@Lorenzo - try the code now. It works in my little test environment I created. – Martin Oct 25 '10 at 17:22
@Martin and Brad: Yes it seems to be working. But there is a problem when I have only one postponement and I the parameter correspond to the postponed appointment – Lorenzo Oct 25 '10 at 17:25
@Lorenzo, the query is designed to operate backwards through time (i.e. gets an appointment's **previous** history). Do you need it to pull the appointment complete history (in both directions)? – Brad Oct 25 '10 at 17:32
Effectively when an Appointment has been postponed only once and I use the id of the latest appointment the query returns no rows. If I use the id of the original appointment the query returns two rows. I am seeing also that the query does not navigate forth more than one level deep while it goes back for multiple levels – Lorenzo Oct 25 '10 at 17:34
@Brad: Yes. The optimum will be that the query will navigate back and forth in the time – Lorenzo Oct 25 '10 at 17:34
@Lorenzo, I added a "forward-looking `UNION` (untested), but I think you should seriously consider using EffectiveDates – Brad Oct 25 '10 at 17:36
@Brad: It would be an option Brad, but not at the moment. It can't be done at this moment. The latest update produce an error with more than 100 recursions... :( – Lorenzo Oct 25 '10 at 17:41
@Lorenzo, I added the `MAXRECURSION` option and set it to 500. You should set this to the smallest value that will yield the results you desire (0 for no limit, but be careful!). http://msdn.microsoft.com/en-us/library/ms181714.aspx http://msdn.microsoft.com/en-us/library/ms175972.aspx – Brad Oct 26 '10 at 12:53
@Brad: Thanks a lot. I can just extimate that number however because I dont know in the real how many times an appointment can be postponed. I will do some query on the database to just estimate that number. Thank you very much for your help :) – Lorenzo Oct 26 '10 at 21:34
Hey, how'd I get jipped out of the "answer"? :) – Brad Oct 27 '10 at 01:52

score 2 · Answer 3 · answered Oct 25 '10 at 17:28

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

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 — Lorenzo, Oct 25 '10 at 17:38

SQL Server Recursive query

3 Answers3

Effective Dating