SQL Server计算当前行和下一行之间的datediff的最佳方法是什么？

I've got the following rough structure:

我有以下粗略结构：

Object -> Object Revisions -> Data

The Data can be shared between several Objects.

数据可以在多个对象之间共享。

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.

我要做的是清理旧的Object Revisions。我想保留第一个，有效的和一系列修订，以便保留一段时间的最后一次更改。数据可能会在2天内发生很大变化，然后单独使用几个月，因此我希望在更改开始之前保留最后一次修订并更新新集。

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...

我目前正在使用光标和临时表来保存更改之间的ID和日期，这样我就可以选择低悬的水果来摆脱。这意味着使用@LastID，@ LastDate，更新和插入临时表等...

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?

是否有更简单/更好的方法来计算初始结果集中当前行和下一行之间的日期差异而不使用游标和临时表？

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

我在sql server 2000上，但会对2005年，2008年的任何新功能感兴趣，这也有助于此。

4 个解决方案

#1

If the identity column is sequential you can use this approach:

如果标识列是顺序的，您可以使用此方法：

SELECT curr.*, DATEDIFF(MINUTE, prev.EventDateTime,curr.EventDateTime) Duration FROM DWLog curr join DWLog prev on prev.EventID = curr.EventID - 1

SELECT curr。*，DATEDIFF（MINUTE，prev.EventDateTime，curr.EventDateTime）持续时间来自DWLog curr加入DWLog prev on prev.EventID = curr.EventID - 1

#2

Here is example SQL. If you have an Identity column, you can use this instead of "ActivityDate".

这是示例SQL。如果您有Identity列，则可以使用此列而不是“ActivityDate”。

SELECT DATEDIFF(HOUR, prev.ActivityDate, curr.ActivityDate)
  FROM MyTable curr
  JOIN MyTable prev
    ON prev.ObjectID = curr.ObjectID
  WHERE prev.ActivityDate =
     (SELECT MAX(maxtbl.ActivityDate)
        FROM MyTable maxtbl
        WHERE maxtbl.ObjectID = curr.ObjectID
          AND maxtbl.ActivityDate < curr.ActivityDate)

I could remove "prev", but have it there assuming you need IDs from it for deleting.

我可以删除“prev”，但是假设你需要从中删除ID。

#3

Hrmm, interesting challenge. I think you can do it without a self-join if you use the new-to-2005 pivot functionality.

嗯，有趣的挑战。如果您使用new-to-2005 pivot功能，我认为您可以在没有自联接的情况下执行此操作。

#4

Here's what I've got so far, I wanted to give this a little more time before accepting an answer.

这是我到目前为止所得到的，我想在接受答案之前多花一点时间。

DECLARE @IDs TABLE 
(
  ID int , 
  DateBetween int
)

DECLARE @OID int
SET @OID = 6150

-- Grab the revisions, calc the datediff, and insert into temp table var.

INSERT @IDs
SELECT ID, 
       DATEDIFF(dd, 
                (SELECT MAX(ActiveDate) 
                 FROM ObjectRevisionHistory 
                 WHERE ObjectID=@OID AND 
                       ActiveDate < ORH.ActiveDate), ActiveDate) 
FROM ObjectRevisionHistory ORH 
WHERE ObjectID=@OID


-- Hard set DateBetween for special case revisions to always keep

 UPDATE @IDs SET DateBetween = 1000 WHERE ID=(SELECT MIN(ID) FROM @IDs)

 UPDATE @IDs SET DateBetween = 1000 WHERE ID=(SELECT MAX(ID) FROM @IDs)

 UPDATE @IDs SET DateBetween = 1000 
 WHERE ID=(SELECT ID 
           FROM ObjectRevisionHistory 
           WHERE ObjectID=@OID AND Active=1)


-- Select out IDs for however I need them

 SELECT * FROM @IDs
 SELECT * FROM @IDs WHERE DateBetween < 2
 SELECT * FROM @IDs WHERE DateBetween > 2

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.

我希望扩展这一点，以便我可以保持最大限度的修改，并在保持第一个，最后一个和活动状态的同时修剪旧版本。应该很容易通过select top和order by子句，嗯...并将ActiveDate扔进临时表。

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.

我得到了彼得的榜样，但是把它修改成了一个子选择。我两个都搞乱了，sql trace显示了subselect做了更少的读取。但它确实有效，当我让我的代表足够高时，我会投票给他。

#1