Calculating Time Interval (Recursive)


  

CREATE TABLE example(
       [ID] [varchar](9) NULL,
       [DTTM] [datetime] NULL,
       [RN0] Int null,
       [COUNTFLAG] int
) ON [PRIMARY] 

Insert into [example] (ID,DTTM) values 
('123456789','2017-10-05 08:00:00.000'), -- Expected Result
('123456789','2017-10-05 08:05:00.000'), 
('123456789','2017-10-05 08:07:00.000'),
('123456789','2017-10-05 08:15:00.000'), -- Expected Result
('123456789','2017-10-05 08:25:00.000'),
('123456789','2017-10-05 10:12:00.000') , -- Expected Result
('123456789','2017-10-05 10:26:00.000'),
('123456789','2017-10-05 10:32:00.000'),  -- Expected Result
('123456789','2017-10-05 10:33:00.000'),
('123456789','2017-10-05 10:34:00.000'),
('123456789','2017-10-05 10:35:00.000'),
('123456789','2017-10-05 10:36:00.000'),
('123456789','2017-10-05 10:37:00.000'),
('123456789','2017-10-05 10:38:00.000'),
('123456789','2017-10-05 10:39:00.000'),
('123456789','2017-10-05 10:40:00.000'),
('123456789','2017-10-05 10:41:00.000'),
('123456789','2017-10-05 10:42:00.000'),
('123456789','2017-10-05 10:43:00.000'),
('123456789','2017-10-05 10:44:00.000'),
('123456789','2017-10-05 10:45:00.000'),
('123456789','2017-10-05 10:46:00.000'),
('123456789','2017-10-05 10:47:00.000'),  -- Expected Result
('123456789','2017-10-05 10:48:00.000')

;with src as (
Select row_number() Over(Partition by ID Order by DTTM) rn
, * from [dbo].[example]
)
--resursive cte
,mycte  as (
Select rn, ID, DTTM, DTTM as DTTM2  from src where rn=1
union all
Select s.rn, s.ID, s.DTTM
, Case when datediff(minute,m.DTTM2, s.DTTM)>=15 then s.DTTM else m .DTTM2 end   

from mycte  m  join src s on s.DTTM>m .DTTM and s.rn=m.rn+1
)
,mycte2 as (
Select ID, DTTM, 
Case when row_number() Over(Partition by ID, DTTM2 Order by DTTM)=1 
then 1 else 0 end CountFlag
from mycte 
)

Select * from mycte2
Where CountFlag=1

drop table [dbo].[example]

 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/42fd69c1-3b2b-48a8-ab57-bce03d611f43/calculating-time-interval?forum=transactsql#ceb08dc3-7f4a-4cf0-8f75-096381722660
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/dcfeb87f-ee4c-4bfe-b8ed-963f88a610a3/time-interval?forum=transactsql



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s