Solved

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

Posted on 2013-10-29
9
230 Views
Last Modified: 2014-01-09
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
0
Comment
Question by:dannygonzalez09
  • 6
  • 3
9 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
Comment Utility
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
 
LVL 5

Author Comment

by:dannygonzalez09
Comment Utility
Thanks Bricrowe...

I regret for not posting it earlier today as i need it tomorrow :(
0
 
LVL 5

Author Comment

by:dannygonzalez09
Comment Utility
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
 
LVL 34

Expert Comment

by:Brian Crowe
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 5

Author Comment

by:dannygonzalez09
Comment Utility
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
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 500 total points
Comment Utility
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
 
LVL 5

Author Comment

by:dannygonzalez09
Comment Utility
Thanks Bricrowe... I'll check the output for the others as well and let you know
0
 
LVL 5

Author Comment

by:dannygonzalez09
Comment Utility
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
 
LVL 5

Author Comment

by:dannygonzalez09
Comment Utility
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

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

771 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now