dannygonzalez09
asked on
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,Total
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
Previous solution and script can be found Here
For Ex:
Id,StartTime,EndTime,Total
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
Brian Crowe
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.
ASKER
Thanks Bricrowe...
I regret for not posting it earlier today as i need it tomorrow :(
I regret for not posting it earlier today as i need it tomorrow :(
ASKER
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
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
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,TotalSec
INTO tmpTable
FROM myTable
WHERE ID = 11140
And then script out that subset only.
Recognizing that the source table is probably a large one you could:
SELECT ID, StartTime,EndTime,TotalSec
INTO tmpTable
FROM myTable
WHERE ID = 11140
And then script out that subset only.
ASKER
CREATE TABLE [dbo].[tmpTable](
[ID] [varchar](50) NULL,
[StartTime] [datetime] NULL,
[EndTime] [datetime] NULL,
[Type] [varchar](50) NULL,
[Comments] [varchar](50) NULL
) ON [PRIMARY]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'')ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Bricrowe... I'll check the output for the others as well and let you know
ASKER
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'')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
)ASKER
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
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'')