Calculate difference between tow recors

I have table that contains below data

  CreatedDate               ID             Message
  2015-05-29 7:00:00      AOOze            abc
  2015-05-29 7:05:00      AOOze            start
  2015-05-29 7:10:00      AOOze            pqy
  2015-05-29 7:15:00      AOOze            lab
  2015-05-29 7:20:00      AOOze            lmn   
  2015-05-29 7:30:00      AOOze            start
  2015-05-29 7:35:00      AOOze            stop
  2015-05-29 7:40:00      AOOze            pqy
  2015-05-29 7:45:00      AOOze            stop
  2015-05-29 7:50:00      AOOze            lmn   

I need to Find Maximum interval time for between message like Start and Stop as per order of createdDate.
For example:

OccuranceCount       MinDate                    MaxDate          DurationInSeconds
1                 2015-05-29 7:05:00      2015-05-29 7:35:00   30
2                 2015-05-29 7:30:00      2015-05-29 7:45:00   15

August 28th, 2015 7:40am

Good day kumaranil,

What is you have data like 

1 start
2 start
3 stop
4 stop
5 start

How can we know which stop related to which start?!?
Is row 3 is the stop of row 1 or row 2?

Please try to clarify your database design using specific e

Free Windows Admin Tool Kit Click here and download it now
August 28th, 2015 4:00pm

DECLARE @YourTable TABLE (
	CreatedDate DATETIME,
	ID VARCHAR(20),
	MESSAGE VARCHAR(20)
)

INSERT INTO @YourTable VALUES 
('2015-05-29 7:00:00', 'AOOze', 'abc'),
('2015-05-29 7:05:00', 'AOOze', 'start'),
('2015-05-29 7:10:00', 'AOOze', 'pqy'),
('2015-05-29 7:15:00', 'AOOze', 'lab'),
('2015-05-29 7:20:00', 'AOOze', 'lmn'),    
('2015-05-29 7:30:00', 'AOOze', 'start'),
('2015-05-29 7:35:00', 'AOOze', 'stop'), 
('2015-05-29 7:40:00', 'AOOze', 'pqy'),
('2015-05-29 7:45:00', 'AOOze', 'stop'),
('2015-05-29 7:50:00', 'AOOze', 'lmn') 

DECLARE @Start TABLE (
	ID INT IDENTITY(1, 1),
	CreatedDate DATETIME
)
DECLARE @Stop TABLE (
	ID INT IDENTITY(1, 1),
	CreatedDate DATETIME
)

INSERT INTO @Start (CreatedDate)
SELECT CreatedDate FROM @YourTable WHERE MESSAGE = 'start' ORDER BY CreatedDate
INSERT INTO @Stop (CreatedDate)
SELECT CreatedDate FROM @YourTable WHERE MESSAGE = 'stop' ORDER BY CreatedDate

SELECT s1.ID, s1.CreatedDate AS MinDate, s2.CreatedDate AS MaxDate, DATEDIFF(MINUTE, s1.CreatedDate, s2.CreatedDate) AS DurationInSeconds
FROM @Start AS s1
INNER JOIN @Stop AS s2 ON s1.ID = s2.ID

August 28th, 2015 4:22pm

Try this:

btw, the duration is in minutes not in seconds.

declare @t table (CreatedDate  datetime,             ID nvarchar(20),            Message nvarchar(50))

insert into @t values
(   '2015-05-29 7:00:00'  ,    'AOOze',            'abc'),
(   '2015-05-29 7:05:00 ' ,    'AOOze',            'start'),
(   '2015-05-29 7:10:00 '  ,   'AOOze' ,          'pqy'),
(   '2015-05-29 7:15:00 '  ,   'AOOze' ,          'lab'),
(   '2015-05-29 7:20:00 ' ,    'AOOze',           'lmn') ,   
(   '2015-05-29 7:30:00 ' ,    'AOOze',           'start'),
(   '2015-05-29 7:35:00 ' ,    'AOOze',            'stop' ),
(   '2015-05-29 7:40:00 ' ,    'AOOze',           'pqy'),
(   '2015-05-29 7:45:00 ' ,    'AOOze',           'stop'),
(   '2015-05-29 7:50:00 ' ,    'AOOze',            'lmn' )

;with cte as
(select ROW_NUMBER() over (order by createddate) as rn,createddate as mindate
 from @t
 where message = 'start'

)
, cte1 as
(
 select ROW_NUMBER() over (order by createddate) as rn1,createddate as maxdate
 from @t
 where message = 'stop'

)
select rn as OccurenceCount, mindate,maxdate,datediff(MINUTE,mindate,maxdate) as DurationinMinutes
from cte join cte1 on rn=rn1

Free Windows Admin Tool Kit Click here and download it now
August 28th, 2015 4:43pm

Appreciate your help. it is very usefull
September 2nd, 2015 12:56am

Post was helpfull
Free Windows Admin Tool Kit Click here and download it now
September 2nd, 2015 12:56am

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

Other recent topics Other recent topics