Roll up data using the time difference between contiguous rows - 2

i got help from Bricrowe regarding this issue but new data has complicated things and i am not getting the results correctly anymore. I'd appreciate, if anyone can help me tweak the existing script to get the correct output

Previous solution and script can be found Here

For Ex:
Id,StartTime,EndTime,TotalSeconds,Type,Comments
11140,10/7/2013 12:33:08,10/7/2013 12:33:10,2,I,
11140,10/7/2013 12:35:53,10/7/2013 12:35:56,3,I,
11140,10/7/2013 12:39:00,10/7/2013 12:39:03,3,I,
11140,10/7/2013 12:40:10,10/7/2013 12:40:11,1,I,
11140,10/7/2013 12:44:30,10/7/2013 12:44:33,3,I,
11140,10/7/2013 12:47:35,10/7/2013 12:47:41,6,I,
11140,10/7/2013 12:47:57,10/7/2013 12:47:58,1,I,

11140,10/7/2013 12:59:13,10/7/2013 12:59:16,3,I,
11140,10/7/2013 13:06:42,10/7/2013 13:06:45,3,I,
11140,10/7/2013 13:10:25,10/7/2013 13:10:26,1,I,
11140,10/7/2013 13:11:03,10/7/2013 13:11:06,3,I,
11140,10/7/2013 13:13:45,10/7/2013 13:14:06,21,I,
11140,10/7/2013 13:15:20,10/8/2013 19:39:30,109450,I,  -- current script ignores this line overlap
11140,10/7/2013 13:15:23,10/7/2013 13:17:09,106,D,
11140,10/8/2013 6:11:26,10/8/2013 6:15:36,250,D,
11140,10/8/2013 19:40:54,10/8/2013 19:40:55,1,I,
11140,10/8/2013 19:41:10,10/8/2013 19:41:20,10,I,
11140,10/8/2013 19:44:51,10/8/2013 19:44:54,3,I,
11140,10/8/2013 19:46:33,10/8/2013 19:46:37,4,I,
11140,10/8/2013 20:00:53,10/8/2013 20:00:54,1,I,

Output:
11140,10/7/2013 12:33:08, 10/7/2013 12:47:58,I
11140,10/7/2013 12:59:13, 10/7/2013 20:00:54,I
LVL 5
dannygonzalez09Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
I don't have time today but I will spend some time on it tomorrow...just wanted to let you know I saw your post.  Maybe someone else can jump in and solve it before then.
0
dannygonzalez09Author Commented:
Thanks Bricrowe...

I regret for not posting it earlier today as i need it tomorrow :(
0
dannygonzalez09Author Commented:
I've attached the file with the data


Also, the correct output for the example i posted above is

Output:
11140,10/7/2013 12:33:08, 10/7/2013 12:47:58,I
11140,10/7/2013 12:59:13, 10/8/2013 19:46:37,I
11140,10/8/2013 20:00:53,10/8/2013 20:00:54,1,I,
Sample-EE.csv
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Brian CroweDatabase AdministratorCommented:
Could you post the script to populate the sample data in a table?  The easiest way to do this is right-click on the database and select Taks -> Generate Scripts and use the wizard to script out the data for that specific object.  That would save quite a bit of time and fat-fingering of the data into a test table.

Recognizing that the source table is probably a large one you could:

SELECT ID, StartTime,EndTime,TotalSeconds,Type,Comments
INTO tmpTable
FROM myTable
WHERE ID = 11140

And then script out that subset only.
0
dannygonzalez09Author Commented:
CREATE TABLE [dbo].[tmpTable](
	[ID] [varchar](50) NULL,
	[StartTime] [datetime] NULL,
	[EndTime] [datetime] NULL,
	[Type] [varchar](50) NULL,
	[Comments] [varchar](50) NULL
) ON [PRIMARY]

Open in new window

SET ANSI_PADDING OFF
GO
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A24B01334040 AS DateTime), CAST(0x0000A24B013343C4 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A24B013343C4 AS DateTime), CAST(0x0000A24B0133461C AS DateTime), N'D', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A24B013B4B3C AS DateTime), CAST(0x0000A24B013B4C68 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A24B013B8124 AS DateTime), CAST(0x0000A24C00C26D0C AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A24B013B882C AS DateTime), CAST(0x0000A24B013BFD5C AS DateTime), N'D', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A24B013ECE10 AS DateTime), CAST(0x0000A24B014021E8 AS DateTime), N'D', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A25000C8D228 AS DateTime), CAST(0x0000A25000C8F2F8 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A25000CEDAB0 AS DateTime), CAST(0x0000A25000CEDD08 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A25000CF9C0C AS DateTime), CAST(0x0000A25000CF9F90 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A25000D07730 AS DateTime), CAST(0x0000A25000D07AB4 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A25000D0C938 AS DateTime), CAST(0x0000A25000D0CA64 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A25000D1F9E8 AS DateTime), CAST(0x0000A25000D1FD6C AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A25000D2D2B4 AS DateTime), CAST(0x0000A25000D2D9BC AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A25000D2EC7C AS DateTime), CAST(0x0000A25000D2EDA8 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A25000D604AC AS DateTime), CAST(0x0000A25000D60830 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A25000D812D8 AS DateTime), CAST(0x0000A25000D8165C AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A25000D9182C AS DateTime), CAST(0x0000A25000D91958 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A25000D944B4 AS DateTime), CAST(0x0000A25000D94838 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A25000DA028C AS DateTime), CAST(0x0000A25000DA1B28 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A25000DA71E0 AS DateTime), CAST(0x0000A2510143F598 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A25000DA7564 AS DateTime), CAST(0x0000A25000DAF19C AS DateTime), N'D', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A25100660468 AS DateTime), CAST(0x0000A25100672960 AS DateTime), N'D', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A25101445808 AS DateTime), CAST(0x0000A25101445934 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A25101446AC8 AS DateTime), CAST(0x0000A25101447680 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A25101456DC4 AS DateTime), CAST(0x0000A25101457148 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A2510145E54C AS DateTime), CAST(0x0000A2510145E9FC AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A2510149D51C AS DateTime), CAST(0x0000A2510149D648 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A25301755DA4 AS DateTime), CAST(0x0000A2530179EF2C AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A25301756830 AS DateTime), CAST(0x0000A2530175C4C4 AS DateTime), N'D', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A253017A564C AS DateTime), CAST(0x0000A253017A96C0 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A253017A60D8 AS DateTime), CAST(0x0000A253017A9C9C AS DateTime), N'D', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A25B010E6450 AS DateTime), CAST(0x0000A25B010E657C AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A2600116532C AS DateTime), CAST(0x0000A2600143FB74 AS DateTime), N'D', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A260011656B0 AS DateTime), CAST(0x0000A2600143FB74 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A2600144F63C AS DateTime), CAST(0x0000A2600147E0B8 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A260014500C8 AS DateTime), CAST(0x0000A2600147E0B8 AS DateTime), N'D', N'')

Open in new window

0
Brian CroweDatabase AdministratorCommented:
Try this...

I added one line and made a very slight change to another.

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 [Sample-EE]
ORDER BY ID, StartTime

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
	
		SELECT @EndTimeCurr = CASE WHEN @EndTimePrev > @EndTimeCurr THEN @EndTimePrev ELSE @EndTimeCurr END

		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 [Sample-EE]
ORDER BY ID, StartTime

SELECT * FROM @CondensedEvents

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dannygonzalez09Author Commented:
Thanks Bricrowe... I'll check the output for the others as well and let you know
0
dannygonzalez09Author Commented:
CREATE TABLE [dbo].[tmpTable](
	[ID] [varchar](50) NULL,
	[StartTime] [datetime] NULL,
	[EndTime] [datetime] NULL,
	[Type] [varchar](50) NULL,
	[Comments] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'9787', CAST(0x0000A24B01761A50 AS DateTime), CAST(0x0000A24B0176586C AS DateTime), N'D', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'9787', CAST(0x0000A24B01761F00 AS DateTime), CAST(0x0000A24B01765998 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'9787', CAST(0x0000A24C00E776B0 AS DateTime), CAST(0x0000A24C01409A9C AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'9787', CAST(0x0000A24C00E7813C AS DateTime), CAST(0x0000A24C00E79528 AS DateTime), N'D', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'9787', CAST(0x0000A2510104C634 AS DateTime), CAST(0x0000A251013A15DC AS DateTime), N'D', N'Cold Start - event [0x00010013])')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'9787', CAST(0x0000A2510104C9B8 AS DateTime), CAST(0x0000A251013A1CE4 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'9787', CAST(0x0000A251013A88B4 AS DateTime), CAST(0x0000A251013A89E0 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'9787', CAST(0x0000A251013A9598 AS DateTime), CAST(0x0000A251013A96C4 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'9787', CAST(0x0000A2520030CFB4 AS DateTime), CAST(0x0000A2520042E118 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'9787', CAST(0x0000A25300E02F68 AS DateTime), CAST(0x0000A25300E08E54 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'9787', CAST(0x0000A25300E039F4 AS DateTime), CAST(0x0000A25300E08D28 AS DateTime), N'D', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'9787', CAST(0x0000A25900FFE178 AS DateTime), CAST(0x0000A259010006F8 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'9787', CAST(0x0000A25900FFED30 AS DateTime), CAST(0x0000A259010004A0 AS DateTime), N'D', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'9787', CAST(0x0000A25A00EEFF98 AS DateTime), CAST(0x0000A25A00F0C3F0 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'9787', CAST(0x0000A25A00EF0A24 AS DateTime), CAST(0x0000A25A00F09E70 AS DateTime), N'D', N'Cold Start - event [0x00010013])')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'9787', CAST(0x0000A25B00A61BD4 AS DateTime), CAST(0x0000A25B00A62C3C AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'9787', CAST(0x0000A25B00A62660 AS DateTime), CAST(0x0000A25B00A629E4 AS DateTime), N'D', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'9787', CAST(0x0000A25B00CB3004 AS DateTime), CAST(0x0000A25B00CFE130 AS DateTime), N'D', N'Cold Start - event [0x00010013])')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'9787', CAST(0x0000A25B00CB3388 AS DateTime), CAST(0x0000A25B00CFF06C AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'9787', CAST(0x0000A25B01436B50 AS DateTime), CAST(0x0000A25B0153D0F8 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'9787', CAST(0x0000A25B014375DC AS DateTime), CAST(0x0000A25B01438AF4 AS DateTime), N'D', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'9787', CAST(0x0000A25B0151812C AS DateTime), CAST(0x0000A25B015185DC AS DateTime), N'D', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'9787', CAST(0x0000A26000378DE0 AS DateTime), CAST(0x0000A2600037BCC0 AS DateTime), N'D', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'9787', CAST(0x0000A26000379164 AS DateTime), CAST(0x0000A2600037BB94 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'9787', CAST(0x0000A26100337C14 AS DateTime), CAST(0x0000A26100342984 AS DateTime), N'D', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'9787', CAST(0x0000A26100337F98 AS DateTime), CAST(0x0000A26100342858 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'9787', CAST(0x0000A26100342858 AS DateTime), CAST(0x0000A26100342858 AS DateTime), N'B', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'9787', CAST(0x0000A26100357B04 AS DateTime), CAST(0x0000A26100359724 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'9787', CAST(0x0000A261003EB32C AS DateTime), CAST(0x0000A261003ECBC8 AS DateTime), N'D', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'9787', CAST(0x0000A261003EB6B0 AS DateTime), CAST(0x0000A261003F17F4 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'9787', CAST(0x0000A26100411B94 AS DateTime), CAST(0x0000A2610041C454 AS DateTime), N'D', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'9787', CAST(0x0000A26100411F18 AS DateTime), CAST(0x0000A2610041C580 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'9787', CAST(0x0000A261004278CC AS DateTime), CAST(0x0000A2610042B490 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'9787', CAST(0x0000A26100428358 AS DateTime), CAST(0x0000A2610042903C AS DateTime), N'D', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'9787', CAST(0x0000A261004434F0 AS DateTime), CAST(0x0000A26100448950 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'9787', CAST(0x0000A26100443F7C AS DateTime), CAST(0x0000A261004456EC AS DateTime), N'D', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'9787', CAST(0x0000A2610044EA94 AS DateTime), CAST(0x0000A2610044F520 AS DateTime), N'D', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'9787', CAST(0x0000A2610044EE18 AS DateTime), CAST(0x0000A26100450EE8 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'9787', CAST(0x0000A26100454278 AS DateTime), CAST(0x0000A26100455088 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'9787', CAST(0x0000A26100459DE0 AS DateTime), CAST(0x0000A2610045BD84 AS DateTime), N'D', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'9787', CAST(0x0000A2610045A164 AS DateTime), CAST(0x0000A2610045BEB0 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'9787', CAST(0x0000A2610046A910 AS DateTime), CAST(0x0000A261004736DC AS DateTime), N'D', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'9787', CAST(0x0000A2610046AC94 AS DateTime), CAST(0x0000A26100473808 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'9787', CAST(0x0000A26100D9B78C AS DateTime), CAST(0x0000A26100DC24A4 AS DateTime), N'D', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'9787', CAST(0x0000A26100D9BB10 AS DateTime), CAST(0x0000A26101594254 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'9787', CAST(0x0000A2610108D47C AS DateTime), CAST(0x0000A26101096824 AS DateTime), N'D', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'9787', CAST(0x0000A261013416B4 AS DateTime), CAST(0x0000A2610134190C AS DateTime), N'D', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A24B01334040 AS DateTime), CAST(0x0000A24B013343C4 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A24B013343C4 AS DateTime), CAST(0x0000A24B0133461C AS DateTime), N'D', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A24B013B4B3C AS DateTime), CAST(0x0000A24B013B4C68 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A24B013B8124 AS DateTime), CAST(0x0000A24C00C26D0C AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A24B013B882C AS DateTime), CAST(0x0000A24B013BFD5C AS DateTime), N'D', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A24B013ECE10 AS DateTime), CAST(0x0000A24B014021E8 AS DateTime), N'D', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A25000C8D228 AS DateTime), CAST(0x0000A25000C8F2F8 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A25000CEDAB0 AS DateTime), CAST(0x0000A25000CEDD08 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A25000CF9C0C AS DateTime), CAST(0x0000A25000CF9F90 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A25000D07730 AS DateTime), CAST(0x0000A25000D07AB4 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A25000D0C938 AS DateTime), CAST(0x0000A25000D0CA64 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A25000D1F9E8 AS DateTime), CAST(0x0000A25000D1FD6C AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A25000D2D2B4 AS DateTime), CAST(0x0000A25000D2D9BC AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A25000D2EC7C AS DateTime), CAST(0x0000A25000D2EDA8 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A25000D604AC AS DateTime), CAST(0x0000A25000D60830 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A25000D812D8 AS DateTime), CAST(0x0000A25000D8165C AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A25000D9182C AS DateTime), CAST(0x0000A25000D91958 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A25000D944B4 AS DateTime), CAST(0x0000A25000D94838 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A25000DA028C AS DateTime), CAST(0x0000A25000DA1B28 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A25000DA71E0 AS DateTime), CAST(0x0000A2510143F598 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A25000DA7564 AS DateTime), CAST(0x0000A25000DAF19C AS DateTime), N'D', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A25100660468 AS DateTime), CAST(0x0000A25100672960 AS DateTime), N'D', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A25101445808 AS DateTime), CAST(0x0000A25101445934 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A25101446AC8 AS DateTime), CAST(0x0000A25101447680 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A25101456DC4 AS DateTime), CAST(0x0000A25101457148 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A2510145E54C AS DateTime), CAST(0x0000A2510145E9FC AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A2510149D51C AS DateTime), CAST(0x0000A2510149D648 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A25301755DA4 AS DateTime), CAST(0x0000A2530179EF2C AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A25301756830 AS DateTime), CAST(0x0000A2530175C4C4 AS DateTime), N'D', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A253017A564C AS DateTime), CAST(0x0000A253017A96C0 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A253017A60D8 AS DateTime), CAST(0x0000A253017A9C9C AS DateTime), N'D', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A25B010E6450 AS DateTime), CAST(0x0000A25B010E657C AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A2600116532C AS DateTime), CAST(0x0000A2600143FB74 AS DateTime), N'D', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A260011656B0 AS DateTime), CAST(0x0000A2600143FB74 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A2600144F63C AS DateTime), CAST(0x0000A2600147E0B8 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'11140', CAST(0x0000A260014500C8 AS DateTime), CAST(0x0000A2600147E0B8 AS DateTime), N'D', N'')

Open in new window


found an issue with another set of data using the new script

Output with above script
ID                     StartTime                                            EndTime
9787      2013-10-02 22:42:04.000      2013-10-02 22:42:58.000
9787      2013-10-03 14:02:44.000      2013-10-03 14:03:10.000 -- Min EndTime
9787      2013-10-08 15:49:27.000      2013-10-08 19:05:23.000
9787      2013-10-09 02:57:43.000      2013-10-09 04:03:30.000
9787      2013-10-10 13:36:14.000      2013-10-10 13:37:34.000
9787      2013-10-16 15:31:38.000      2013-10-16 15:32:08.000

Output wth previous script
                   StartTime                           EndTime
2013-10-02 22:42:04.000      2013-10-02 22:42:58.000
2013-10-03 14:02:53.000      2013-10-03 19:27:17.000 -- Max Endtime
2013-10-08 15:49:27.000      2013-10-08 19:05:23.000

I added a case statement (last line of the below snippet) to correct the value and it seemed to fix the problem but not sure if that's the right way to handle it...can you have a look ..Thx

IF @ID0 = @IDCurr
	BEGIN
	
		SELECT @EndTimeCurr = CASE WHEN @EndTimePrev > @EndTimeCurr THEN @EndTimePrev ELSE @EndTimeCurr END

		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)
				CASE WHEN ISNULL(@EndTimePrev, @EndTime0) > @EndTime0 THEN ISNULL(@EndTimePrev, @EndTime0) ELSE @EndTime0 END
				
			)

Open in new window

0
dannygonzalez09Author Commented:
Although the change i made above fixed an issue, i now see this for below data set

Output :
ID                        StartTime                             EndTime
40977      2013-10-08 06:11:26.000      2013-10-08 06:15:36.000
40977      2013-10-10 22:39:26.000      2013-10-10 22:56:00.000
40977      2013-10-10 22:57:32.000      2013-10-10 22:58:29.000

-- Line 2 & 3 should be merged together and show up as one with EndTime - 2013-10-10 22:58:29 as they are within the interval

INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'40977', CAST(0x0000A24B013343C4 AS DateTime), CAST(0x0000A24B0133461C AS DateTime), N'D', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'40977', CAST(0x0000A24B013B8124 AS DateTime), CAST(0x0000A24C00C26D0C AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'40977', CAST(0x0000A24B013B882C AS DateTime), CAST(0x0000A24B013BFD5C AS DateTime), N'D', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'40977', CAST(0x0000A24B013ECE10 AS DateTime), CAST(0x0000A24B014021E8 AS DateTime), N'D', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'40977', CAST(0x0000A24D0051C78C AS DateTime), CAST(0x0000A24D0053C1CC AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'40977', CAST(0x0000A25000DA71E0 AS DateTime), CAST(0x0000A25101445808 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'40977', CAST(0x0000A25000DA7564 AS DateTime), CAST(0x0000A25000DAF19C AS DateTime), N'D', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'40977', CAST(0x0000A25100660468 AS DateTime), CAST(0x0000A25100672960 AS DateTime), N'D', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'40977', CAST(0x0000A25301756128 AS DateTime), CAST(0x0000A2530179EE00 AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'40977', CAST(0x0000A25301756830 AS DateTime), CAST(0x0000A2530175C4C4 AS DateTime), N'D', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'40977', CAST(0x0000A253017A59D0 AS DateTime), CAST(0x0000A253017A9C9C AS DateTime), N'I', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'40977', CAST(0x0000A253017A60D8 AS DateTime), CAST(0x0000A253017A9C9C AS DateTime), N'D', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'40977', CAST(0x0000A2600116532C AS DateTime), CAST(0x0000A2600143FB74 AS DateTime), N'D', N'')
INSERT [dbo].[tmpTable] ([ID], [StartTime], [EndTime], [Type], [Comments]) VALUES (N'40977', CAST(0x0000A260014500C8 AS DateTime), CAST(0x0000A2600147E0B8 AS DateTime), N'D', N'')

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.