当日期位于同一个表的不同行中时，如何查找日期差异？

Question

我有一个表::

ItemID  VersionNo  CreatedDate
-------------------------------
1       3          7/9/2010
1       2          7/3/2010
1       1          5/3/2010
1       0          3/3/2010
2       0          4/4/2010
3       1          4/5/2010
3       0          3/4/2010

...其中版本 0 表示 .. 它是新生产的项目。这里我需要找到时间（两个版本之间的时间差距）并添加一列作为处理时间。 就像::

ItemID  VersionNo  CreatedDate  ProcessTime
-------------------------------------------
1       3          7/9/2010     6Days or 6*24Hrs
1       2          7/3/2010     60Days 
1       1          5/3/2010     2Days
1       0          3/3/2010     ''
2       0          4/4/2010     '' 
3       1          4/5/2010     31Days
3       0          3/4/2010     ''

版本号不是固定的..意味着随着时间的推移，它可能会增加...如何在 MS Access 或 SQL-Server 中实现所需的结果。

预先感谢您的所有真诚努力。 谢谢

Answer 1

怎么样（访问）：

SELECT t.ItemID, 
       t.VersionNo, 
       t.CreatedDate, (
          SELECT Top 1 
          CreatedDate 
          FROM Versions v 
          WHERE v.ItemID=t.ItemID 
          And v.VersionNo<t.VersionNo 
          ORDER BY VersionNo DESC) AS LastDate, 
       DateDiff("h",[LastDate],[CreatedDate]) AS DiffHrs,
       DateDiff("d",[LastDate],[CreatedDate]) AS DiffDays
FROM Versions t

Answer 2

将表与其自身连接起来，如下所示 (SQL Server)：

-- create the table and your data
create table #x (ItemID int, VersionNo int, CreatedDate datetime)
go
insert into #x
select 1,       3          ,'7/9/2010'
union all select  1       ,2          ,'7/3/2010'
union all select  1       ,1          ,'5/3/2010'
union all select  1       ,0          ,'3/3/2010'
union all select  2       ,0          ,'4/4/2010'
union all select  3       ,1          ,'4/5/2010'
union all select  3       ,0          ,'3/4/2010'
go

-- The query
select v2.ItemID, v2.VersionNo, datediff(dd, v1.CreatedDate, v2.CreatedDate)
from #x v1, #x v2
where v1.ItemID = v2.ItemID and v1.VersionNo + 1 = v2.VersionNo

Answer 3

这是在 Access SQL 中，使用 3 个查询，每个步骤一个。

查询1，在itemID上进行自连接，其中版本号较小：

    SELECT t1.itemID, t1.versionNo, t1.created, t2.versionNo AS t2Version
FROM Table1 AS t1 INNER JOIN Table1 AS t2 ON t1.itemID = t2.itemID
WHERE (t2.versionNo)<[t1].[versionNo];

查询2，限制较小版本号的最大值：

    SELECT q1.itemID, q1.versionNo, q1.created, Max(q1.t2Version) AS MaxOft2Version
FROM Query1 AS q1
GROUP BY q1.itemID, q1.versionNo, q1.created;

查询3，现在执行datediff：

SELECT q2.itemID, q2.versionNo, q2.created, q2.MaxOft2Version, t1.created, 
    DateDiff("d",[t1].[created],[Q2].[created]) AS daysdiff
FROM Query2 AS q2 INNER JOIN Table1 AS t1 
    ON (q2.MaxOft2Version = t1.versionNo) 
    AND (q2.itemID = t1.itemID);

Answer 4

SQL Server 2005，用于处理 VersionNo 存在间隙的情况。

-- Declare a query that extends your table with a new column 
-- that is the sequentially numbered representation of VersionNo.
-- This could be a view, but I used a CTE.  I am going to use this
-- query twice below.
WITH Sequential AS (select *, 
    RANK() over (partition by ItemId order by VersionNo) as SequentialVersionNo
from #T as x
)
select 
    v.ItemID, v.VersionNo, v.SequentialVersionNo, v.CreatedDate, 
    DATEDIFF(day, vPrior.CreatedDate, v.CreatedDate) as ProcessTime
from Sequential as v
left outer join Sequential as vPrior 
on v.ItemID=vPrior.ItemID 
and v.SequentialVersionNo = vPrior.SequentialVersionNo+1;