calculate running difference for each row

[zalim30]

hi,
consider the following table

TID DocID Date
885328 090041d3800bff2d 2005-10-29 12:36:31.940
885393 090041d3800bff2d 2005-5-29 12:51:58.067
885403 090041d3800bff2d 2005-3-29 12:55:58.967
885557 090041d3800bff2d 2005-2-29 13:40:47.773
885569 090041d3800bff2d 2005-2-29 13:43:55.027

where TID is the trackID, DocID is document ID and Date is the date this document was accessed. I need to know the differences between each successive Date by months. so I need the following answer
TID DocID Difference
885328 090041d3800bff2d 5
885393 090041d3800bff2d 2
885403 090041d3800bff2d 1
885557 090041d3800bff2d 0
885569 090041d3800bff2d -

Please help.
Thanks in advance!

 

[rac2]

SELECT a.tid,
       a.docid,
      ( SELECT DATEDIFF(month, b.Date, a.Date)
        FROM MyTable b
        WHERE b.tid = (
                     SELECT MAX(c.tid)
                     FROM MyTable c
                     WHERE c.docid = a.docid
                       AND c.tid < a.tid
                     )
      )
FROM MyTable a

Or

SELECT a.tid,
       a.docid,
       DATEDIFF(month, b.Date, a.Date)
FROM MyTable a
JOIN  ( SELECT c.Date, c.tid, c.docid
         FROM MyTable c
	) b ON b.docid = a.docid
	AND b.tid = (
                     SELECT MAX(d.SurveyID)
                     FROM MyTable d
                     WHERE d.tid < a.tid
                     )

The first approach places the complex part in a subquery in the SELECT list. It is somewhat easier to see what is happening that way.

The second approach places the complex part in a subquery in a JOIN. It seems a lot more complicated, using MyTable in four ways.

That the JOIN would be more complex surprised me. I looked at the execution plans in Query Analyzer expecting them to produce the same result and was again surprised to see the second approach was more efficient. It had one-third the cost of the first one.

I believe these queries are fairly general solutions to this type of problem. Except for the DATEDIFF() function which is specific to SQL Server.

 

[zalim30]

Rac2, thanx for spending time on the problem. Both of them are good for me.
Thanks again.