how to find date difference when dates are places in different rows in same table?

Question

I have a table::

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

...where Version 0 means .. its a newly produced item. Here I need to find time,(time gap between two versions) and add a column as process time. like::

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

VersionNo's are not Fixed..means with time, it could increase... How to acheive the desire result in MS Access or in SQL-Server.

Thanks in advance for all your sincere efforts. Thanks

Answer 1

Join the table with itself, like this (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 2

How about (Access):

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 3

Here it is in Access SQL, using 3 queries, one for each step.

Query1, self-join on itemID where versionNo is smaller:

    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];

Query2, limit to max of smaller versionNos:

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

Query3, now do 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, to handle the case where there are gaps in 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;