Time Interval

Hi All,

I have a simple table as shown:



I want to have values on the last column to represent the time interval between the 2 date columns (visits); i.e for event-ID 2 for example, I will have entry(EventID = 2)  - exit(EventID = 1), and so on

Please assist

Many thanks

Regards

Zimiso


May 20th, 2015 7:35am

Hi Zimiso,

  you need to create 2 subqueries

SELECT Subquery1.Entry, Subquery2.Exit, Datediff("dd",Subquery1.Entry, Subquery2.Exit) Diff

FROM

(

SELECT Rank() over(order by EventID) rank_event, *

FROM Table 1) AS Subquery1

INNER JOIN

(

SELECT Rank() over(order by EventID) rank_event, *

FROM Table 1) AS Subquery2 on Subquery1.rank_event=Subquery2.rank_event+1

This is the initial logic, if the ranks are not sequentials which I think it should be since we have ranked by EventId then you will require to improve on this.

Regards,

Bharath R S

Free Windows Admin Tool Kit Click here and download it now
May 20th, 2015 1:08pm

Hi Zimiso,

Are you looking for a sample as below?

DECLARE @T TABLE(EventID INT, [entry] DATETIME, [exit] DATETIME)

INSERT INTO @T VALUES
(1,'20070101 06:34:12','20070101 12:45:34'),
(2,'20070102 13:23:08','20070102 15:05:37'),
(3,'20070103 16:34:12','20070103 16:55:18'),
(4,'20070104 17:02:00','20070104 18:13:21'),
(5,'20070105 18:16:55','20070105 19:28:48'),
(6,'20070106 19:59:11','20070107 21:23:11'),
(7,'20070107 22:12:23','20070107 22:40:25')
 

;WITH Cte AS
(
SELECT *, ROW_NUMBER() OVER(ORDER BY EventID) rn FROM @T
),
Cte2 AS
(
SELECT t1.*,DATEDIFF(SECOND,T1.[exit],T2.[entry]) Seconds
FROM Cte T1 LEFT JOIN Cte T2
			ON T1.RN =T2.EventID-1
)
SELECT EventID,[entry],[exit],
LTRIM(STR(Seconds/86400))+' day '+
RIGHT('0'+LTRIM(STR(Seconds%86400)/3600),2)+':'+
RIGHT('0'+LTRIM(STR(Seconds%3600)/60),2)+':'+
RIGHT('0'+LTRIM(STR(Seconds%60)),2) INTERVAL
FROM Cte2
  

If you have any question, feel free to let me know.

May 21st, 2015 3:21am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics