Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to implement datetime difference 1

Status
Not open for further replies.

Jony77

IS-IT--Management
May 4, 2008
9
0
0
LU
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
 
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
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top