How to implement datetime difference 1

[Jony77]

Hello,

I have a fact table which has a column called TimeDiff and will contain the time difference b/w the two adjacent rows in another column called TimeCol. Assume TimeCol has the following entries:

TimeCol
2002-09-05 14:12:00
2002-09-05 14:12:05
2002-09-05 14:12:09
2002-09-05 14:12:45

Then the column TimeDiff will be:

TimeDiff
0
5
4
36

Please can someone give me an idea on how to implement it. The TimeCol has upto 1200 rows.

Thanks,

Jony

 

[Craig0201]

You start somewhere like this in your ETL

SELECT qryDiff.TimeCol, Min(qryDiff.Diff) AS MinOfDiff
FROM (
SELECT Time.TimeCol, (Time.TimeCol - Time_1.TimeCol) AS Diff
FROM [Time], [Time] AS Time_1
WHERE Time.TimeCol - Time_1.TimeCol > 0
) AS qryDiff
GROUP BY qryDiff.TimeCol;

You can then update back the time differences. You will get a problem where you have multiple events with identical times. You need to be sure this isn't happening before applying the solution.

C

 

[Hans63]

This is dependend on the environment you will build this in. Oracle has analytics extensions to SQL which allow you to access the next row from a resultset. With this, calculating a time-difference gets easy.

I do not know if SQL-server, sybase or DB2 or ... has a similar feature.