<p>I've got the following rough structure:</p>

<pre><code>Object -&gt; Object Revisions -&gt; Data
</code></pre>

<p>The Data can be shared between several Objects.</p>

<p>What I'm trying to do is clean out old Object Revisions. I want to keep the first, active, and a spread of revisions so that the last change for a time period is kept. The Data might be changed a lot over the course of 2 days then left alone for months, so I want to keep the last revision before the changes started and the end change of the new set.</p>

<p>I'm currently using a cursor and temp table to hold the IDs and date between changes so I can select out the low hanging fruit to get rid of. This means using @LastID, @LastDate, updates and inserts to the temp table, etc... </p>

<p>Is there an easier/better way to calculate the date difference between the current row and the next row in my initial result set without using a cursor and temp table? </p>

<p>I'm on sql server 2000, but would be interested in any new features of 2005, 2008 that could help with this as well.</p>

[{'answer_id': 52455, 'author': 'Peter', 'author_id': 5189, 'author_profile': 'https://Stackoverflow.com/users/5189', 'pm_score': 2, 'selected': False, 'text': '<p>Here is example SQL.  If you have an Identity column, you can use this instead of "ActivityDate".</p>\n\n<pre><code>SELECT DATEDIFF(HOUR, prev.ActivityDate, curr.ActivityDate)\n  FROM MyTable curr\n  JOIN MyTable prev\n    ON prev.ObjectID = curr.ObjectID\n  WHERE prev.ActivityDate =\n     (SELECT MAX(maxtbl.ActivityDate)\n        FROM MyTable maxtbl\n        WHERE maxtbl.ObjectID = curr.ObjectID\n          AND maxtbl.ActivityDate &lt; curr.ActivityDate)\n</code></pre>\n\n<p>I could remove "prev", but have it there assuming you need IDs from it for deleting.</p>\n'}, {'answer_id': 52522, 'author': 'Danimal', 'author_id': 2757, 'author_profile': 'https://Stackoverflow.com/users/2757', 'pm_score': 0, 'selected': False, 'text': '<p>Hrmm, interesting challenge. I think you can do it without a self-join if you use the new-to-2005 pivot functionality.</p>\n'}, {'answer_id': 52706, 'author': 'ddowns', 'author_id': 5201, 'author_profile': 'https://Stackoverflow.com/users/5201', 'pm_score': 0, 'selected': False, 'text': "<p>Here's what I've got so far, I wanted to give this a little more time before accepting an answer.</p>\n\n<pre><code>DECLARE @IDs TABLE \n(\n  ID int , \n  DateBetween int\n)\n\nDECLARE @OID int\nSET @OID = 6150\n\n-- Grab the revisions, calc the datediff, and insert into temp table var.\n\nINSERT @IDs\nSELECT ID, \n       DATEDIFF(dd, \n                (SELECT MAX(ActiveDate) \n                 FROM ObjectRevisionHistory \n                 WHERE ObjectID=@OID AND \n                       ActiveDate &lt; ORH.ActiveDate), ActiveDate) \nFROM ObjectRevisionHistory ORH \nWHERE ObjectID=@OID\n\n\n-- Hard set DateBetween for special case revisions to always keep\n\n UPDATE @IDs SET DateBetween = 1000 WHERE ID=(SELECT MIN(ID) FROM @IDs)\n\n UPDATE @IDs SET DateBetween = 1000 WHERE ID=(SELECT MAX(ID) FROM @IDs)\n\n UPDATE @IDs SET DateBetween = 1000 \n WHERE ID=(SELECT ID \n           FROM ObjectRevisionHistory \n           WHERE ObjectID=@OID AND Active=1)\n\n\n-- Select out IDs for however I need them\n\n SELECT * FROM @IDs\n SELECT * FROM @IDs WHERE DateBetween &lt; 2\n SELECT * FROM @IDs WHERE DateBetween &gt; 2\n</code></pre>\n\n<p>I'm looking to extend this so that I can keep at maximum so many revisions, and prune off the older ones while still keeping the first, last, and active. Should be easy enough through select top and order by clauses, um... and tossing in ActiveDate into the temp table.</p>\n\n<p>I got Peter's example to work, but took that and modified it into a subselect. I messed around with both and the sql trace shows the subselect doing less reads. But it does work and I'll vote him up when I get my rep high enough.</p>\n"}, {'answer_id': 21113658, 'author': 'cs981khx', 'author_id': 3193950, 'author_profile': 'https://Stackoverflow.com/users/3193950', 'pm_score': 2, 'selected': True, 'text': '<p>If the identity column is sequential you can use this approach:</p>\n\n<p><code>SELECT curr.*, DATEDIFF(MINUTE, prev.EventDateTime,curr.EventDateTime) Duration FROM DWLog curr join DWLog prev on prev.EventID = curr.EventID - 1</code></p>\n'}]
['https://Stackoverflow.com/questions/52430', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/5201/']