Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Roll up data using the time difference between contiguous rows

Posted on 2013-09-24
19
Medium Priority
?
712 Views
Last Modified: 2013-10-29
Hi

I am trying to find the time difference between contiguous rows and if its with in a certain interval roll them up as 1 outage

In the below example RowId time diff between Endtime of RowId -135 and StartTime of RowId -136 is greater than 10 mins and same between RowId - 136 & 137.. so they'll be treated as separate outages but time difference between the contiguous rows from 137 upto 157 is with 10 mins, in this case roll it up ...the result should be as below

There are may other columns involved... i'm hoping that i'll be able to get the rest of the issue resolved if i can roll these up together ...

So far, I've tried to use CTE with RowNum function to join the consecutive rows and get the time diff but it isn't working out..

For Ex:
RowId      Id      StartTime      EndTime
135      8526      9/1/2013 17:35:07      9/1/2013 17:38:36
136      8526      9/1/2013 18:47:18      9/1/2013 18:49:07
137      8526      9/2/2013 14:08:12      9/2/2013 14:08:15
138      8526      9/2/2013 14:10:33      9/2/2013 14:10:44
139      8526      9/2/2013 14:12:25      9/2/2013 14:12:34
140      8526      9/2/2013 14:12:38      9/2/2013 14:13:00
141      8526      9/2/2013 14:13:11      9/2/2013 14:13:12
142      8526      9/2/2013 14:13:13      9/2/2013 14:13:15
143      8526      9/2/2013 14:13:31      9/2/2013 14:14:37
144      8526      9/2/2013 14:14:44      9/2/2013 14:15:14
145      8526      9/2/2013 14:15:18      9/2/2013 14:15:30
146      8526      9/2/2013 14:15:36      9/2/2013 14:15:43
147      8526      9/2/2013 14:15:48      9/2/2013 14:16:02
148      8526      9/2/2013 14:16:06      9/2/2013 14:16:15
149      8526      9/2/2013 14:18:53      9/2/2013 14:18:55
150      8526      9/2/2013 14:19:04      9/2/2013 14:19:07
151      8526      9/2/2013 14:19:15      9/2/2013 14:19:20
152      8526      9/2/2013 14:19:24      9/2/2013 14:19:25
153      8526      9/2/2013 14:19:36      9/2/2013 14:19:49
154      8526      9/2/2013 14:19:54      9/2/2013 14:19:58
155      8526      9/2/2013 14:20:02      9/2/2013 14:20:21
156      8526      9/2/2013 14:20:33      9/2/2013 14:20:42
157      8526      9/2/2013 14:27:13      9/2/2013 14:27:22
158      8526      9/2/2013 16:34:10      9/2/2013 16:35:23

Output:

135      8526      9/1/2013 17:35:07      9/1/2013 17:38:36
136      8526      9/1/2013 18:47:18      9/1/2013 18:49:07
137      8526      9/2/2013 14:08:12      9/2/2013 14:27:22
158      8526      9/2/2013 16:34:10      9/2/2013 16:35:23
0
Comment
Question by:dannygonzalez09
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
  • 3
  • +1
19 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39518323
Here's an article i wrote on T-SQL:  Identifying bad dates in a time series.  There's a screen shot about half way down that demonstrates a series of data, with a time_variance column that displays the difference in days.

Feel free to copy this code into your SSMS, run to verify it works, then change the DATEDIFF line so that it does minutes instead of days.  Then, edit the final SELECT so that it shows only rows where the time_variance > 10.

Enjoy.
0
 
LVL 5

Author Comment

by:dannygonzalez09
ID: 39518451
I'm not sure if you misunderstood my question.... I want to get all the values but combine only those where the time difference between end time and start time of the contiguous row is less than 10 mins
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39518490
Define 'combine'.   If I'm reading this correctly, since we're not assigning new ID values based on whether time_variance > 10 or not, the solution should still work.
0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39518515
I think you're going to have to use a cursor to accomplish this if the cte method isn't working.  Just cycle through the records ordered appropriately.  Compare the current record with the previous (save the pertinent values to variables) and either discard or insert the current record in a temp table.
0
 
LVL 5

Author Comment

by:dannygonzalez09
ID: 39518573
well, i was doing the same with CTE but wasn't able to get the correct results... I'm sure i am missing something in the logic... Can you help me with the code

Thanks
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39518582
Can you post some schema information or the cte code you were trying?
0
 
LVL 5

Author Comment

by:dannygonzalez09
ID: 39518760
Here's the code i have so far.... the output is incorrect as its not getting the correct values for the 1st and the last time intervals .. the code gets the Min and Max RowId from which i'm to get the time stamps in the next step

WITH CTE AS
 (
	SELECT *,(Row_Number() OVER (PARTITION BY ModelKey ORDER BY StartTime)) as RowNum
	--, CASE WHEN TotalSeconds > 600 THEN 0 ELSE 1 END as SameOutage
	FROM SampleData
	WHERE ModelKey = 8526 
 )
 
,CTERowNum AS
( 
SELECT X.*, ABS(DATEDIFF(SS,EndTime,NxtStartTime)) as Diff
, CASE WHEN ABS(DATEDIFF(SS,EndTime,NxtStartTime)) < 600 THEN 1 ELSE 0 END as SameOutage
FROM
	(
SELECT C.RowId,C.ModelKey, C.StartTime, C.EndTime,C.RowNum ,C2.StartTime as NxtStartTime, C2.EndTime as NxtEndTime,C2.RowNum as NxtRowNum
FROM CTE C
LEFT JOIN CTE C2 
ON C.ModelKey = C2.ModelKey AND C.RowNum = C2.RowNum-1 and C.EndTime < C2.StartTime
	)X
)

SELECT MIN(RowId) as Id,ModelKey, MIN(StartTime) as StartTime, Min(TotalSec),
CASE WHEN MIN(TotalSec) > 600 THEN MAX(EndTime) ELSE MAX(NxtEndTime) END as NxtEnd, MIN(MinRow) as MinRow, MAX(MaxRow) as MaxRow
FROM
(
SELECT RowId,ModelKey,StartTime,EndTime,NxtStartTime,NxtEndTime, ABS(DATEDIFF(SS,NxtStartTime,EndTime)) as TotalSec
, (select TOP 1 RowId FROM CTERowNum C2 WHERE C2.ModelKey = C.ModelKey and isnull(C2.SameOutage,0)<>1 and C2.RowNum < C.RowNum Order by RowId desc) as MinRow
, (select TOP 1 RowId FROM CTERowNum C2 WHERE C2.ModelKey = C.ModelKey and isnull(C2.SameOutage,0)<>1 and C2.RowNum > C.RowNum Order by RowId asc)-1 as MaxRow
FROM CTERowNum C
)X
GROUP BY ModelKey,MinRow,MaxRow
Order by StartTime

Open in new window

Sample.xlsx
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39518958
I'm working on a cursor solution but it's going to take me a while to get it written and tested.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39518970
dannygonzalez09 - Let me know if you intend to read my article or not, as it uses CTE's and looks similar to what you have posted above.
0
 
LVL 5

Author Comment

by:dannygonzalez09
ID: 39519038
Thanks BriCrowe

@Jimhorn - Yes, i looked at your article as soon as you posted it.... it basically does the same thing that i was working on upto an extent, but i couldn't make mine work yet and i am stuck..

Sorry, but it din't help me much
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39519108
Okay try this...change the tablename "myEvent" to your tablename

Let me know if you have any problems.  I tested it against a sample set with a second ID value.

SET NOCOUNT ON;

DECLARE @ID0		INT,
	@StartTime0		DATETIME,
	@EndTime0		DATETIME,
	@IDPrev			INT			= NULL,
	@StartTimePrev	DATETIME	= NULL,
	@EndTimePrev	DATETIME	= NULL,
	@IDCurr			INT			= NULL,
	@StartTimeCurr	DATETIME	= NULL,
	@EndTimeCurr	DATETIME	= NULL,
	@Interval		INT,		--Interval Threshold in seconds
	@RowCount		INT
	
DECLARE @CondensedEvents TABLE
(
	ID				INT,
	StartTime		DATETIME,
	EndTime			DATETIME
	--Include any other columns here
)

SELECT @Interval = 600			--10 minutes

DECLARE crsEvent CURSOR FOR
SELECT ID, StartTime, EndTime
FROM myEvent
ORDER BY ID, EndTime

OPEN crsEvent

FETCH NEXT FROM crsEvent
INTO @ID0, @StartTime0, @EndTime0

WHILE @@FETCH_STATUS = 0
BEGIN
	IF @IDCurr IS NULL
	BEGIN
		PRINT 'No Current Record'
		FETCH NEXT FROM crsEvent
		INTO @IDCurr, @StartTimeCurr, @EndTimeCurr
		
	END
	
	PRINT '0 - ID[' + ISNULL(CAST(@ID0 AS VARCHAR), '') + '] StartTime[' + ISNULL(CONVERT(VARCHAR, @StartTime0, 120), '') + '] EndTime[' + ISNULL(CONVERT(VARCHAR, @EndTime0, 120), '') + ']'
	PRINT 'P - ID[' + ISNULL(CAST(@IDPrev AS VARCHAR), '') + '] StartTime[' + ISNULL(CONVERT(VARCHAR, @StartTimePrev, 120), '') + '] EndTime[' + ISNULL(CONVERT(VARCHAR, @EndTimePrev, 120), '') + ']'
	PRINT 'C - ID[' + ISNULL(CAST(@IDCurr AS VARCHAR), '') + '] StartTime[' + ISNULL(CONVERT(VARCHAR, @StartTimeCurr, 120), '') + '] EndTime[' + ISNULL(CONVERT(VARCHAR, @EndTimeCurr, 120), '') + ']'

	IF @ID0 = @IDCurr
	BEGIN
		--PRINT 'Same ID Value: ' + CAST(@IDCurr AS VARCHAR)
		
		IF DATEDIFF(SECOND, @EndTime0, @EndTimeCurr) > @Interval
		BEGIN
			PRINT 'Interval Exceeded[' + CAST(DATEDIFF(SECOND, @EndTime0, @EndTimeCurr) AS VARCHAR) + ']'
			--Record the condensed record
			PRINT 'Inserting record matching: ID[' + CAST(ISNULL(@IDPrev, @IDCurr) AS VARCHAR) +
				'] StartTime[' + CONVERT(VARCHAR, ISNULL(@StartTimePrev, @StartTimeCurr), 120) +
				'] EndTime[' + CONVERT(VARCHAR, ISNULL(@EndTimePrev, @EndTimeCurr), 120) + ']'
			INSERT INTO @CondensedEvents
			(
				ID,
				StartTime,
				EndTime
			)
			SELECT ID, StartTime, EndTime --Other columns
			FROM myEvent
			WHERE ID = ISNULL(@IDPrev, @IDCurr)
				AND StartTime = ISNULL(@StartTimePrev, @StartTimeCurr)
				AND EndTime = ISNULL(@EndTimePrev, @EndTimeCurr)

			SET @RowCount = @@ROWCOUNT
			IF @RowCount = 0		--This shouldn't happen but I had a bug I had to track down
			BEGIN
				PRINT 'NO MATCHING RECORD FOUND'
			END
			
			SELECT @ID0 = @IDCurr,
				@StartTime0 = @StartTimeCurr,
				@EndTime0 = @EndTimeCurr
		END
		ELSE	--Still within range of interval
		BEGIN
			PRINT 'Interval NOT Exceeded'
			SELECT @IDPrev = @IDCurr,
				@StartTimePrev = @StartTimeCurr,
				@EndTimePrev = @EndTimeCurr
		END
	END
	ELSE	--Reset everything for the new ID value
	BEGIN
		SELECT @ID0 = @IDCurr,
			@StartTime0 = @StartTimeCurr,
			@EndTime0 = @EndTimeCurr,
			@IDPrev = NULL, @StartTimePrev = NULL, @EndTimePrev = NULL,
			@IDCurr = NULL, @StartTimeCurr = NULL, @EndTimeCurr = NULL
	END
	
	--Get the next record
	FETCH NEXT FROM crsEvent
	INTO @IDCurr, @StartTimeCurr, @EndTimeCurr
	
	PRINT ''
END
CLOSE crsEvent
DEALLOCATE crsEvent

SELECT * FROM myEvent WHERE ID = 8526
SELECT * FROM @CondensedEvents

Open in new window

0
 
LVL 5

Author Comment

by:dannygonzalez09
ID: 39519628
Thanks BriCrowe

I ran the script but looks like its not getting what i need (probably because i messed up something)

I've found another useful link and seems to almost get the result set i need... i still need to change it to calculate the time difference using the Start and Endtime (of the next row) instead of using start times

http://sqlmag.com/t-sql/grouping-web-site-hits-sessions
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39519688
I see what I was doing wrong...I'll make the adjustments tomorrow.
0
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 1400 total points
ID: 39521621
I misunderstood the logic.  Try the procedure below, if the logic works then we'll have to determine how to get the other columns in.  Which record of a series would you include?

SET NOCOUNT ON;

DECLARE @ID0		INT,
	@StartTime0		DATETIME,
	@EndTime0		DATETIME,
	@IDPrev			INT			= NULL,
	@StartTimePrev	DATETIME	= NULL,
	@EndTimePrev	DATETIME	= NULL,
	@IDCurr			INT			= NULL,
	@StartTimeCurr	DATETIME	= NULL,
	@EndTimeCurr	DATETIME	= NULL,
	@Interval		INT			--Interval Threshold in seconds
	
DECLARE @CondensedEvents TABLE
(
	ID				INT,
	StartTime		DATETIME,
	EndTime			DATETIME
)

SELECT @Interval = 600			--10 minutes

DECLARE crsEvent CURSOR FOR
SELECT ID, StartTime, EndTime
FROM myEvent
ORDER BY ID, EndTime

OPEN crsEvent

FETCH NEXT FROM crsEvent
INTO @ID0, @StartTime0, @EndTime0

WHILE @@FETCH_STATUS = 0
BEGIN
	IF @IDCurr IS NULL
	BEGIN
		PRINT 'No Current Record'
		FETCH NEXT FROM crsEvent
		INTO @IDCurr, @StartTimeCurr, @EndTimeCurr
		
	END
	
	PRINT '0 - ID[' + ISNULL(CAST(@ID0 AS VARCHAR), '') + '] StartTime[' + ISNULL(CONVERT(VARCHAR, @StartTime0, 120), '') + '] EndTime[' + ISNULL(CONVERT(VARCHAR, @EndTime0, 120), '') + ']'
	PRINT 'P - ID[' + ISNULL(CAST(@IDPrev AS VARCHAR), '') + '] StartTime[' + ISNULL(CONVERT(VARCHAR, @StartTimePrev, 120), '') + '] EndTime[' + ISNULL(CONVERT(VARCHAR, @EndTimePrev, 120), '') + ']'
	PRINT 'C - ID[' + ISNULL(CAST(@IDCurr AS VARCHAR), '') + '] StartTime[' + ISNULL(CONVERT(VARCHAR, @StartTimeCurr, 120), '') + '] EndTime[' + ISNULL(CONVERT(VARCHAR, @EndTimeCurr, 120), '') + ']'

	IF @ID0 = @IDCurr
	BEGIN
		--PRINT 'Same ID Value: ' + CAST(@IDCurr AS VARCHAR)
		
		IF DATEDIFF(SECOND, ISNULL(@EndTimePrev, @EndTime0), @StartTimeCurr) > @Interval
		BEGIN
			PRINT 'Interval Exceeded[' + CAST(DATEDIFF(SECOND, @EndTime0, @EndTimeCurr) AS VARCHAR) + ']'
			--Record the condensed record
			PRINT 'Inserting record: ID[' + CAST(@ID0 AS VARCHAR) +
				'] StartTime[' + CONVERT(VARCHAR, @StartTime0, 120) +
				'] EndTime[' + CONVERT(VARCHAR, ISNULL(@EndTimePrev, @EndTime0), 120) + ']'
			INSERT INTO @CondensedEvents
			(
				ID,
				StartTime,
				EndTime
			)
			VALUES
			(
				@ID0,
				@StartTime0,
				ISNULL(@EndTimePrev, @EndTime0)
			)
			
			SELECT @ID0 = @IDCurr,
				@StartTime0 = @StartTimeCurr,
				@EndTime0 = @EndTimeCurr,
				@IDPrev = NULL,
				@StartTimePrev = NULL,
				@EndTimePrev = NULL
				
		END
		ELSE	--Still within range of interval
		BEGIN
			PRINT 'Interval NOT Exceeded'
			SELECT @IDPrev = @IDCurr,
				@StartTimePrev = @StartTimeCurr,
				@EndTimePrev = @EndTimeCurr
		END
	END
	ELSE	--Close off the ID series and reset everything for the new ID value
	BEGIN
		PRINT 'Inserting record: ID[' + CAST(ISNULL(@IDPrev, @ID0) AS VARCHAR) +
			'] StartTime[' + CONVERT(VARCHAR, @StartTime0, 120) +
			'] EndTime[' + CONVERT(VARCHAR, ISNULL(@EndTimePrev, @EndTime0), 120) + ']'

		INSERT INTO @CondensedEvents
        (
			ID,
			StartTime,
			EndTime
        )
		VALUES
		(
			ISNULL(@IDPrev, @ID0),
			@StartTime0,
			ISNULL(@EndTimePrev, @EndTime0)
		)
		
		SELECT @ID0 = @IDCurr,
			@StartTime0 = @StartTimeCurr,
			@EndTime0 = @EndTimeCurr,
			@IDPrev = NULL, @StartTimePrev = NULL, @EndTimePrev = NULL,
			@IDCurr = NULL, @StartTimeCurr = NULL, @EndTimeCurr = NULL
	END
	
	--Get the next record
	FETCH NEXT FROM crsEvent
	INTO @IDCurr, @StartTimeCurr, @EndTimeCurr
	
	--Check for end last record and close it off
	IF @@FETCH_STATUS <> 0
	BEGIN
		PRINT 'Inserting record: ID[' + CAST(ISNULL(@IDPrev, @ID0) AS VARCHAR) +
			'] StartTime[' + CONVERT(VARCHAR, @StartTime0, 120) +
			'] EndTime[' + CONVERT(VARCHAR, ISNULL(@EndTimePrev, @EndTime0), 120) + ']'

		INSERT INTO @CondensedEvents
        (
			ID,
			StartTime,
			EndTime
        )
		VALUES
		(
			ISNULL(@IDPrev, @ID0),
			@StartTime0,
			ISNULL(@EndTimePrev, @EndTime0)
		)
	END
	
	PRINT ''
END
CLOSE crsEvent
DEALLOCATE crsEvent

SELECT * FROM @CondensedEvents

Open in new window

0
 
LVL 5

Author Comment

by:dannygonzalez09
ID: 39521769
Thanks BriCrowe

Perfect, exactly what i needed...

I was also able to modify the script (from the link i posted yesterday) to get all the output i needed, currently testing its performance which so far has been good

I'll compare the performance on both and use the best, will post a comment soon if i need more help on your SQL

Thanks again
0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 600 total points
ID: 39529834
3 variants produce this result:
    | ROWID |   ID |           STARTTIME |             ENDTIME | MIN_PREV_DIFF |
    |-------|------|---------------------|---------------------|---------------|
    |   135 | 8526 | 2013-09-02 03:35:07 | 2013-09-02 03:38:36 |        (null) |
    |   136 | 8526 | 2013-09-02 04:47:18 | 2013-09-02 04:49:07 |            74 |
    |   137 | 8526 | 2013-09-03 00:08:12 | 2013-09-03 00:08:15 |          1161 |
    |   158 | 8526 | 2013-09-03 02:34:10 | 2013-09-03 02:35:23 |           128 |

Open in new window

one using rowID, another using row_number (which is may be more reliable than using rowID), or if you happen to use SQL 2012 then use the LAG() funcion which is both reliable and more efficient than the others (as it requires just one pass of the data).

Here are the details of the queries etc.
**Query 1**:

    /* any SQL Server version, if rowID is usable */
    
    SELECT
            t1.rowid
          , t1.id
          , convert(varchar, t1.starttime ,120)          starttime
          , convert(varchar, t1.endtime ,120)            endtime
          , datediff(MINUTE, t2.starttime , t1.endtime ) min_prev_diff
    FROM table1 AS t1
    LEFT JOIN table1 AS t2 ON t1.rowid = t2.rowid + 1
    WHERE t2.starttime IS NULL
    OR datediff(MINUTE, t2.starttime , t1.endtime ) > 10
    

**[Results][2]**:
    
    | ROWID |   ID |           STARTTIME |             ENDTIME | MIN_PREV_DIFF |
    |-------|------|---------------------|---------------------|---------------|
    |   135 | 8526 | 2013-09-02 03:35:07 | 2013-09-02 03:38:36 |        (null) |
    |   136 | 8526 | 2013-09-02 04:47:18 | 2013-09-02 04:49:07 |            74 |
    |   137 | 8526 | 2013-09-03 00:08:12 | 2013-09-03 00:08:15 |          1161 |
    |   158 | 8526 | 2013-09-03 02:34:10 | 2013-09-03 02:35:23 |           128 |


**Query 2**:

    /* use of CTE with row_number() */
    
    ;WITH
    CTE AS (
           SELECT
                    rowid
                  , id
                  , starttime
                  , endtime
                  , row_number() over (ORDER BY starttime) AS rn
           FROM table1
           )
    SELECT
            t1.rowid
          , t1.id
          , convert(varchar, t1.starttime ,120)          starttime
          , convert(varchar, t1.endtime ,120)            endtime
          , datediff(MINUTE, t2.starttime , t1.endtime ) min_prev_diff
    FROM CTE AS t1
    LEFT JOIN CTE AS t2 ON t1.rn = t2.rn + 1
    WHERE t2.starttime IS NULL
    OR datediff(MINUTE, t2.starttime , t1.endtime ) > 10
    

**[Results][3]**:
    
    | ROWID |   ID |           STARTTIME |             ENDTIME | MIN_PREV_DIFF |
    |-------|------|---------------------|---------------------|---------------|
    |   135 | 8526 | 2013-09-02 03:35:07 | 2013-09-02 03:38:36 |        (null) |
    |   136 | 8526 | 2013-09-02 04:47:18 | 2013-09-02 04:49:07 |            74 |
    |   137 | 8526 | 2013-09-03 00:08:12 | 2013-09-03 00:08:15 |          1161 |
    |   158 | 8526 | 2013-09-03 02:34:10 | 2013-09-03 02:35:23 |           128 |


**Query 3**:

    /* SQL 2012 on (requires LAG() , this is more efficient*/
    SELECT
            t1.rowid
          , t1.id
          , convert(varchar, t1.starttime ,120)      starttime
          , convert(varchar, t1.endtime ,120)        endtime
          , datediff(MINUTE, lagstart , t1.endtime ) min_prev_diff
    FROM (
           SELECT
                    rowid
                  , id
                  , starttime
                  , endtime
                  , lag(starttime) over (ORDER BY rowid) AS lagstart
           FROM table1
         ) AS t1
    WHERE lagstart IS NULL
    OR datediff(MINUTE, lagstart, t1.endtime ) > 10
    

**[Results][4]**:
    
    | ROWID |   ID |           STARTTIME |             ENDTIME | MIN_PREV_DIFF |
    |-------|------|---------------------|---------------------|---------------|
    |   135 | 8526 | 2013-09-02 03:35:07 | 2013-09-02 03:38:36 |        (null) |
    |   136 | 8526 | 2013-09-02 04:47:18 | 2013-09-02 04:49:07 |            74 |
    |   137 | 8526 | 2013-09-03 00:08:12 | 2013-09-03 00:08:15 |          1161 |
    |   158 | 8526 | 2013-09-03 02:34:10 | 2013-09-03 02:35:23 |           128 |



  [1]: http://sqlfiddle.com/#!6/38d54/11

=======================

    CREATE TABLE Table1
    	([RowId] int, [Id] int, [StartTime] datetime, [EndTime] datetime)
    ;
    	
    INSERT INTO Table1
    	([RowId], [Id], [StartTime], [EndTime])
    VALUES
    	(135, 8526, '2013-09-02 03:35:07', '2013-09-02 03:38:36'),
    	(136, 8526, '2013-09-02 04:47:18', '2013-09-02 04:49:07'),
    	(137, 8526, '2013-09-03 00:08:12', '2013-09-03 00:08:15'),
    	(138, 8526, '2013-09-03 00:10:33', '2013-09-03 00:10:44'),
    	(139, 8526, '2013-09-03 00:12:25', '2013-09-03 00:12:34'),
    	(140, 8526, '2013-09-03 00:12:38', '2013-09-03 00:13:00'),
    	(141, 8526, '2013-09-03 00:13:11', '2013-09-03 00:13:12'),
    	(142, 8526, '2013-09-03 00:13:13', '2013-09-03 00:13:15'),
    	(143, 8526, '2013-09-03 00:13:31', '2013-09-03 00:14:37'),
    	(144, 8526, '2013-09-03 00:14:44', '2013-09-03 00:15:14'),
    	(145, 8526, '2013-09-03 00:15:18', '2013-09-03 00:15:30'),
    	(146, 8526, '2013-09-03 00:15:36', '2013-09-03 00:15:43'),
    	(147, 8526, '2013-09-03 00:15:48', '2013-09-03 00:16:02'),
    	(148, 8526, '2013-09-03 00:16:06', '2013-09-03 00:16:15'),
    	(149, 8526, '2013-09-03 00:18:53', '2013-09-03 00:18:55'),
    	(150, 8526, '2013-09-03 00:19:04', '2013-09-03 00:19:07'),
    	(151, 8526, '2013-09-03 00:19:15', '2013-09-03 00:19:20'),
    	(152, 8526, '2013-09-03 00:19:24', '2013-09-03 00:19:25'),
    	(153, 8526, '2013-09-03 00:19:36', '2013-09-03 00:19:49'),
    	(154, 8526, '2013-09-03 00:19:54', '2013-09-03 00:19:58'),
    	(155, 8526, '2013-09-03 00:20:02', '2013-09-03 00:20:21'),
    	(156, 8526, '2013-09-03 00:20:33', '2013-09-03 00:20:42'),
    	(157, 8526, '2013-09-03 00:27:13', '2013-09-03 00:27:22'),
    	(158, 8526, '2013-09-03 02:34:10', '2013-09-03 02:35:23')
    ;

Open in new window

0
 
LVL 5

Author Closing Comment

by:dannygonzalez09
ID: 39534167
Thank you BriCrowe and Paul
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39535088
No problem, thanks for the assist. Cheers, Paul.
0
 
LVL 5

Author Comment

by:dannygonzalez09
ID: 39610003
BriCrowe, I've been using your script which worked like a charm but new data is messing up the output...can you please help
 
I've opened another question here
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_28280456.html

Thanks
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question