Find time different based on Type by making group

Question

Find time different based on Type by making group

shamshad.ali

Hall of Fame

Points: 3850
More actions
July 8, 2019 at 12:14 pm

#3660728

I have data like attached picture, I want to make group each event when it starts from 1 and end till its max of eventId = 14 and calculate the time difference ... as shown in picture.
If there are 8 EventType with 1 then it should have max of 8 eventType with EventType 14. To make pair. Min of EventId is 1 and max of event Id is 14 to make pair and calculate time difference between them.
Out put
EventType StartTime EndTime difference
1
Attachments:
You must be logged in to view attached files.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

You've been here long enough to know that providing pictures is not the best way of asking for help. Instead, please provide DDL, sample data in the form of INSERT statements and desired results.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

shamshad.ali Hall of Fame Points: 3850 More actions · Answer 2

CREATE TABLE [dbo].[tblEvent](
[eventId] [varchar](50) NULL,
[netobjectid] [varchar](50) NULL,
[EventTime] [datetime] NULL,
[EventType] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[tblEvent] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'33995300', N'6', CAST(N'2019-06-12T01:32:00.000' AS DateTime), 1)
GO
INSERT [dbo].[tblEvent] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'33996144', N'6', CAST(N'2019-06-12T02:11:00.000' AS DateTime), 14)
GO
INSERT [dbo].[tblEvent] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'33996145', N'6', CAST(N'2019-06-12T02:11:00.000' AS DateTime), 14)
GO
INSERT [dbo].[tblEvent] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'33996154', N'6', CAST(N'2019-06-12T02:11:00.000' AS DateTime), 5001)
GO
INSERT [dbo].[tblEvent] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'33996197', N'6', CAST(N'2019-06-12T02:11:00.000' AS DateTime), 14)
GO
INSERT [dbo].[tblEvent] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34047857', N'6', CAST(N'2019-06-13T15:03:00.000' AS DateTime), 1)
GO
INSERT [dbo].[tblEvent] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34047941', N'6', CAST(N'2019-06-13T15:08:00.000' AS DateTime), 5001)
GO
INSERT [dbo].[tblEvent] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34048005', N'6', CAST(N'2019-06-13T15:10:00.000' AS DateTime), 14)
GO
INSERT [dbo].[tblEvent] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34161848', N'6', CAST(N'2019-06-18T16:08:00.000' AS DateTime), 1)
GO
INSERT [dbo].[tblEvent] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34161886', N'6', CAST(N'2019-06-18T16:13:00.000' AS DateTime), 14)
GO
INSERT [dbo].[tblEvent] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34161887', N'6', CAST(N'2019-06-18T16:13:00.000' AS DateTime), 5001)
GO
INSERT [dbo].[tblEvent] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34184693', N'6', CAST(N'2019-06-20T02:13:00.000' AS DateTime), 1)
GO
INSERT [dbo].[tblEvent] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34184899', N'6', CAST(N'2019-06-20T02:31:00.000' AS DateTime), 14)
GO
INSERT [dbo].[tblEvent] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34184916', N'6', CAST(N'2019-06-20T02:32:00.000' AS DateTime), 5001)
GO
INSERT [dbo].[tblEvent] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34194557', N'6', CAST(N'2019-06-20T17:13:00.000' AS DateTime), 1)
GO
INSERT [dbo].[tblEvent] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34194579', N'6', CAST(N'2019-06-20T17:16:00.000' AS DateTime), 14)
GO
INSERT [dbo].[tblEvent] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34194583', N'6', CAST(N'2019-06-20T17:16:00.000' AS DateTime), 5001)
GO
INSERT [dbo].[tblEvent] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34289465', N'6', CAST(N'2019-06-24T16:20:00.000' AS DateTime), 1)
GO
INSERT [dbo].[tblEvent] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34289616', N'6', CAST(N'2019-06-24T16:25:00.000' AS DateTime), 14)
GO
INSERT [dbo].[tblEvent] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34289630', N'6', CAST(N'2019-06-24T16:25:00.000' AS DateTime), 5001)
GO
INSERT [dbo].[tblEvent] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34312506', N'6', CAST(N'2019-06-24T22:19:00.000' AS DateTime), 1)
GO
INSERT [dbo].[tblEvent] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34312560', N'6', CAST(N'2019-06-24T22:22:00.000' AS DateTime), 14)
GO
INSERT [dbo].[tblEvent] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34312574', N'6', CAST(N'2019-06-24T22:23:00.000' AS DateTime), 5001)
GO
INSERT [dbo].[tblEvent] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34576929', N'6', CAST(N'2019-07-02T10:39:00.000' AS DateTime), 14)
GO
INSERT [dbo].[tblEvent] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34576930', N'6', CAST(N'2019-07-02T10:39:00.000' AS DateTime), 14)
GO
INSERT [dbo].[tblEvent] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34576935', N'6', CAST(N'2019-07-02T10:39:00.000' AS DateTime), 14)
GO
INSERT [dbo].[tblEvent] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34707649', N'6', CAST(N'2019-07-05T12:06:00.000' AS DateTime), 1)
GO
INSERT [dbo].[tblEvent] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34707696', N'6', CAST(N'2019-07-05T12:11:00.000' AS DateTime), 5001)
GO
INSERT [dbo].[tblEvent] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34707705', N'6', CAST(N'2019-07-05T12:11:00.000' AS DateTime), 14)
GO
INSERT [dbo].[tblEvent] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34707706', N'6', CAST(N'2019-07-05T12:11:00.000' AS DateTime), 14)
GO
INSERT [dbo].[tblEvent] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34707708', N'6', CAST(N'2019-07-05T12:12:00.000' AS DateTime), 14)
GO

EventIdOfEventType1EventTimeOfEventType1EventIdOfEventType14EventTimeOfEventType14BeforeSecondEventType1DifferenceBetweenCol2AndCol4
339953002019-06-12 01:32:16.000339961972019-06-12 02:11:53.0002377 (39 minutes)
340478572019-06-13 15:03:58.000340480052019-06-13 15:10:01.000363 (7 minutes

drew.allen SSC Guru Points: 76985 More actions · Answer 3

Your sample data has times to the nearest minute. Your expected results has times to the nearest second. There is no way that we can derive data with greater precision than the data input.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

DesNorton SSC-Insane Points: 24386 More actions · Answer 4

Your posted DDL seems to have different values to your expected outcome values.

That said, I believe that the following code should get you on your way

WITH cteStart AS (
  SELECT
      s.netobjectid
    , EventIdOfEventType1   = s.eventId
    , EventTimeOfEventType1 = s.EventTime
    , nxtEventID   = LEAD(s.eventId)   OVER (PARTITION BY s.netobjectid ORDER BY s.eventId)
    , nxtEventTime = LEAD(s.EventTime) OVER (PARTITION BY s.netobjectid ORDER BY s.eventId)
  FROM #tblEvent AS s
  WHERE s.EventType = 1
)
SELECT cs.netobjectid
     , cs.EventIdOfEventType1
     , cs.EventTimeOfEventType1
     , ce.EventIdOfEventType14
     , ce.EventTimeOfEventType14
     , TimeDiffInSeconds = DATEDIFF(SECOND, cs.EventTimeOfEventType1, ce.EventTimeOfEventType14)
FROM cteStart AS cs
OUTER APPLY (SELECT TOP(1) e.netobjectid
                         , EventIdOfEventType14   = e.eventId
                         , EventTimeOfEventType14 = e.EventTime
             FROM #tblEvent AS e
             WHERE e.netobjectid = cs.netobjectid
               AND e.EventType = 14
               AND e.eventId > cs.EventIdOfEventType1
               AND e.eventId <= ISNULL(cs.nxtEventID, e.eventId)
            ORDER BY e.eventId DESC
            ) AS ce

How to post data/code on a forum to get the best help.

shamshad.ali Hall of Fame Points: 3850 More actions · Answer 5

Thanks, it worked for new. However, I have some other cases for which I tried to make changes in your given query to get desired results. I have placing my data script in another table and the script is as follows:

CREATE TABLE [dbo].[tblEventCases](
[eventId] [varchar](50) NULL,
[netobjectid] [varchar](50) NULL,
[EventTime] [datetime] NULL,
[EventType] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'33995300', N'6', CAST(N'2019-06-12T01:32:00.000' AS DateTime), 1)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'33995311', N'6', CAST(N'2019-06-12T01:33:00.000' AS DateTime), 5000)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'33996141', N'6', CAST(N'2019-06-12T02:10:00.000' AS DateTime), 5)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'33996144', N'6', CAST(N'2019-06-12T02:11:00.000' AS DateTime), 14)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'33996145', N'6', CAST(N'2019-06-12T02:11:00.000' AS DateTime), 14)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'33996154', N'6', CAST(N'2019-06-12T02:11:00.000' AS DateTime), 5001)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'33996197', N'6', CAST(N'2019-06-12T02:11:00.000' AS DateTime), 14)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34047857', N'6', CAST(N'2019-06-13T15:03:00.000' AS DateTime), 1)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34047860', N'6', CAST(N'2019-06-13T15:04:00.000' AS DateTime), 5000)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34047915', N'6', CAST(N'2019-06-13T15:08:00.000' AS DateTime), 5)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34047941', N'6', CAST(N'2019-06-13T15:08:00.000' AS DateTime), 5001)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34048005', N'6', CAST(N'2019-06-13T15:10:00.000' AS DateTime), 14)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34161848', N'6', CAST(N'2019-06-18T16:08:00.000' AS DateTime), 1)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34161854', N'6', CAST(N'2019-06-18T16:09:00.000' AS DateTime), 5000)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34161881', N'6', CAST(N'2019-06-18T16:12:00.000' AS DateTime), 5)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34161886', N'6', CAST(N'2019-06-18T16:13:00.000' AS DateTime), 14)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34161887', N'6', CAST(N'2019-06-18T16:13:00.000' AS DateTime), 5001)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34184693', N'6', CAST(N'2019-06-20T02:13:00.000' AS DateTime), 1)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34184698', N'6', CAST(N'2019-06-20T02:14:00.000' AS DateTime), 5000)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34184897', N'6', CAST(N'2019-06-20T02:31:00.000' AS DateTime), 5)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34184899', N'6', CAST(N'2019-06-20T02:31:00.000' AS DateTime), 14)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34184916', N'6', CAST(N'2019-06-20T02:32:00.000' AS DateTime), 5001)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34194557', N'6', CAST(N'2019-06-20T17:13:00.000' AS DateTime), 1)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34194561', N'6', CAST(N'2019-06-20T17:14:00.000' AS DateTime), 5000)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34194574', N'6', CAST(N'2019-06-20T17:16:00.000' AS DateTime), 5)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34194579', N'6', CAST(N'2019-06-20T17:16:00.000' AS DateTime), 14)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34194583', N'6', CAST(N'2019-06-20T17:16:00.000' AS DateTime), 5001)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34289465', N'6', CAST(N'2019-06-24T16:20:00.000' AS DateTime), 1)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34289469', N'6', CAST(N'2019-06-24T16:20:00.000' AS DateTime), 5000)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34289579', N'6', CAST(N'2019-06-24T16:24:00.000' AS DateTime), 5)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34289616', N'6', CAST(N'2019-06-24T16:25:00.000' AS DateTime), 14)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34289630', N'6', CAST(N'2019-06-24T16:25:00.000' AS DateTime), 5001)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34312506', N'6', CAST(N'2019-06-24T22:19:00.000' AS DateTime), 1)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34312508', N'6', CAST(N'2019-06-24T22:19:00.000' AS DateTime), 5000)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34312548', N'6', CAST(N'2019-06-24T22:22:00.000' AS DateTime), 5)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34312560', N'6', CAST(N'2019-06-24T22:22:00.000' AS DateTime), 14)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34312574', N'6', CAST(N'2019-06-24T22:23:00.000' AS DateTime), 5001)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34576929', N'6', CAST(N'2019-07-02T10:39:00.000' AS DateTime), 14)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34576930', N'6', CAST(N'2019-07-02T10:39:00.000' AS DateTime), 14)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34576935', N'6', CAST(N'2019-07-02T10:39:00.000' AS DateTime), 14)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34707649', N'6', CAST(N'2019-07-05T12:06:00.000' AS DateTime), 1)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34707650', N'6', CAST(N'2019-07-05T12:07:00.000' AS DateTime), 5000)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34707694', N'6', CAST(N'2019-07-05T12:11:00.000' AS DateTime), 5)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34707696', N'6', CAST(N'2019-07-05T12:11:00.000' AS DateTime), 5001)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34707705', N'6', CAST(N'2019-07-05T12:11:00.000' AS DateTime), 14)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34707706', N'6', CAST(N'2019-07-05T12:11:00.000' AS DateTime), 14)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34707708', N'6', CAST(N'2019-07-05T12:12:00.000' AS DateTime), 14)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34009380', N'693', CAST(N'2019-06-12T11:55:00.000' AS DateTime), 1)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34009392', N'693', CAST(N'2019-06-12T11:56:00.000' AS DateTime), 1)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34009393', N'693', CAST(N'2019-06-12T11:56:00.000' AS DateTime), 5000)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34010111', N'693', CAST(N'2019-06-12T12:28:00.000' AS DateTime), 5)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34010125', N'693', CAST(N'2019-06-12T12:28:00.000' AS DateTime), 5001)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34010164', N'693', CAST(N'2019-06-12T12:29:00.000' AS DateTime), 14)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34010165', N'693', CAST(N'2019-06-12T12:29:00.000' AS DateTime), 14)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34010176', N'693', CAST(N'2019-06-12T12:30:00.000' AS DateTime), 14)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34208766', N'693', CAST(N'2019-06-21T14:20:00.000' AS DateTime), 1)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34208815', N'693', CAST(N'2019-06-21T14:21:00.000' AS DateTime), 5)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34208821', N'693', CAST(N'2019-06-21T14:21:00.000' AS DateTime), 5000)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34208823', N'693', CAST(N'2019-06-21T14:21:00.000' AS DateTime), 5001)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34208852', N'693', CAST(N'2019-06-21T14:26:00.000' AS DateTime), 1)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34208861', N'693', CAST(N'2019-06-21T14:27:00.000' AS DateTime), 5000)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34208876', N'693', CAST(N'2019-06-21T14:28:00.000' AS DateTime), 14)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34208877', N'693', CAST(N'2019-06-21T14:28:00.000' AS DateTime), 14)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34208878', N'693', CAST(N'2019-06-21T14:28:00.000' AS DateTime), 5)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34208880', N'693', CAST(N'2019-06-21T14:29:00.000' AS DateTime), 14)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34208888', N'693', CAST(N'2019-06-21T14:29:00.000' AS DateTime), 5001)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34517854', N'693', CAST(N'2019-06-30T22:35:00.000' AS DateTime), 14)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34281055', N'3384', CAST(N'2019-06-24T10:21:00.000' AS DateTime), 1)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34281073', N'3384', CAST(N'2019-06-24T10:22:00.000' AS DateTime), 5000)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34291071', N'3384', CAST(N'2019-06-24T17:07:00.000' AS DateTime), 5)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34291092', N'3384', CAST(N'2019-06-24T17:08:00.000' AS DateTime), 5001)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34291399', N'3384', CAST(N'2019-06-24T17:19:00.000' AS DateTime), 14)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34291897', N'3384', CAST(N'2019-06-24T17:34:00.000' AS DateTime), 100)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34303596', N'3384', CAST(N'2019-06-24T19:23:00.000' AS DateTime), 1)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34303642', N'3384', CAST(N'2019-06-24T19:24:00.000' AS DateTime), 5000)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34303740', N'3384', CAST(N'2019-06-24T19:25:00.000' AS DateTime), 5)
GO
INSERT [dbo].[tblEventCases] ([eventId], [netobjectid], [EventTime], [EventType]) VALUES (N'34303809', N'3384', CAST(N'2019-06-24T19:26:00.000' AS DateTime), 5001)
GO

And my query to see other netobjetIds cases is here:

---------------------- for testing 
;
WITH cteStart AS (
SELECT
      s.netobjectid
    , EventIdOfEventType1   = s.eventId
    , EventTimeOfEventType1 = s.EventTime
    , nxtEventID   = LEAD(s.eventId)   OVER (PARTITION BY s.netobjectid ORDER BY s.eventId)
    , nxtEventTime = LEAD(s.EventTime) OVER (PARTITION BY s.netobjectid ORDER BY s.eventId)
  FROM  tblEventCases  AS s
  WHERE s.EventType = 1 --and  NetObjectID =  @netObjectId
  )
  SELECT cs.netobjectid
     , cs.EventIdOfEventType1
     , cs.EventTimeOfEventType1
     , case when ce.EventIdOfEventType14 is null then ce2.EventIdOfEventType5001 else ce.EventIdOfEventType14 end as EventIdOfEventType
     , case when ce.EventTimeOfEventType14 is null then ce2.EventTimeOfEventType5001 else ce.EventTimeOfEventType14 end as EventTimeOfEventType
     , TimeDiffInSeconds = DATEDIFF(SECOND, cs.EventTimeOfEventType1, ISNULL(ce.EventTimeOfEventType14,ce2.EventTimeOfEventType5001))
-- , RIGHT ('0' + CONVERT(varchar(6), (DATEDIFF(MILLISECOND,cs.EventTimeOfEventType1, ISNULL(ce.EventTimeOfEventType14,ce2.EventTimeOfEventType5001) )/1000)/3600),2)
--+ ':' + RIGHT('0' + CONVERT(varchar(2), ((DATEDIFF(MILLISECOND,cs.EventTimeOfEventType1, ISNULL(ce.EventTimeOfEventType14,ce2.EventTimeOfEventType5001) )/1000) % 3600) / 60), 2)
--+ ':' + RIGHT('0' + CONVERT(varchar(2), (DATEDIFF(MILLISECOND,cs.EventTimeOfEventType1, ISNULL(ce.EventTimeOfEventType14,ce2.EventTimeOfEventType5001) )/1000) % 60), 2)
--[HH:MM:SS]
FROM cteStart AS cs
OUTER APPLY (SELECT TOP(1) e.netobjectid
                         , EventIdOfEventType14   = e.eventId
                         , EventTimeOfEventType14 = e.EventTime
             FROM tblEventCases AS e
             WHERE e.netobjectid = cs.netobjectid
               AND e.EventType = 14  --and  NetObjectID = @netObjectId
               AND e.eventId > cs.EventIdOfEventType1
               AND e.eventId <= ISNULL(cs.nxtEventID, e.eventId)
            ORDER BY e.eventId DESC
            ) AS ce
OUTER APPLY (SELECT TOP(1) e2.netobjectid
                         , EventIdOfEventType5001   = e2.eventId
                         , EventTimeOfEventType5001 = e2.EventTime
             FROM tblEventCases AS e2
             WHERE e2.netobjectid = cs.netobjectid
               AND e2.EventType = 5001  --and  NetObjectID = @netObjectId
               AND e2.eventId > cs.EventIdOfEventType1
               AND e2.eventId <= ISNULL(ce.EventIdOfEventType14,e2.EventID)
            ORDER BY e2.eventId DESC
            ) AS ce2

The result for netobjectId 3384 and 6 is OK, but for 693 its incorrect and because of the data and condition in query does not seems correct to handle this case.

see the row 11 and 13 having same data in column 4.

Hope you understand how to fix this case ...

DesNorton SSC-Insane Points: 24386 More actions · Answer 6

You need to include cs.nxtEventID in the filter for EventType = 5001

DECLARE @netObjectId varchar(50) = '693';

WITH cteStart AS (
  SELECT
      s.netobjectid
    , EventIdOfEventType1   = s.eventId
    , EventTimeOfEventType1 = s.EventTime
    , nxtEventID            = LEAD(s.eventId)   OVER (PARTITION BY s.netobjectid ORDER BY s.eventId)
    , nxtEventTime          = LEAD(s.EventTime) OVER (PARTITION BY s.netobjectid ORDER BY s.eventId)
  FROM dbo.tblEventCases AS s
  WHERE s.EventType = 1
    AND s.netobjectid = @netObjectId
)
SELECT  cs.netobjectid
      , cs.EventIdOfEventType1
      , cs.EventTimeOfEventType1
      , EventIdOfEventType   = ISNULL( ce.EventIdOfEventType14  , ce2.EventIdOfEventType5001   ) -- No need for the CASE statement
      , EventTimeOfEventType = ISNULL( ce.EventTimeOfEventType14, ce2.EventTimeOfEventType5001 ) -- No need for the CASE statement
      , TimeDiffInSeconds    = DATEDIFF( SECOND, cs.EventTimeOfEventType1
                                       , ISNULL( ce.EventTimeOfEventType14, ce2.EventTimeOfEventType5001 )
                                       )
FROM cteStart AS cs
OUTER APPLY (SELECT TOP(1) e.netobjectid
                         , EventIdOfEventType14   = e.eventId
                         , EventTimeOfEventType14 = e.EventTime
             FROM dbo.tblEventCases AS e
             WHERE e.netobjectid = cs.netobjectid
               AND e.EventType   = 14
               AND e.eventId     > cs.EventIdOfEventType1
               AND e.eventId     <= ISNULL(cs.nxtEventID, e.eventId)
            ORDER BY e.eventId DESC
            ) AS ce
OUTER APPLY (SELECT TOP(1) e2.netobjectid
                         , EventIdOfEventType5001   = e2.eventId
                         , EventTimeOfEventType5001 = e2.EventTime
             FROM dbo.tblEventCases AS e2
             WHERE e2.netobjectid = cs.netobjectid
               AND e2.EventType   = 5001
               AND e2.eventId     > cs.EventIdOfEventType1
               AND e2.eventId     <= COALESCE(cs.nxtEventID, ce.EventIdOfEventType14, e2.EventID) -- Need to include cs.nxtEventID 
             ORDER BY e2.eventId DESC
            ) AS ce2;

How to post data/code on a forum to get the best help.

drew.allen SSC Guru Points: 76985 More actions · Answer 7

I believe that this gives you the expected results (although it is different from the results that Des' solution gives). It only requires a single scan of the table.

WITH netobjectidgroups AS
(
SELECT *
,CASE WHEN EventType = 1 THEN eventID END AS EventIdOfEventType1
,CASE WHEN EventType = 1 THEN eventTime END AS EventTimeOfEventType1
,CASE WHEN EventType = 14 THEN eventID END AS EventIdOfEventType14
,CASE WHEN EventType = 14 THEN eventTime END AS EventTimeOfEventType14
,SUM(CASE WHEN ec.EventType = 1 THEN 1 ELSE 0 END) OVER(PARTITION BY netobjectid ORDER BY EventTime ROWS UNBOUNDED PRECEDING)  AS EventStartCnt
FROM #EventCases AS ec
)
SELECT
g.netobjectid
,MIN(EventIdOfEventType1) AS EventIdOfEventType1
,MIN(EventTimeOfEventType1) AS EventTimeOfEventType1
,MAX(EventIdOfEventType14) AS EventIdOfEventType14
,MAX(EventTimeOfEventType14) AS EventTimeOfEventType14
,DATEDIFF(SECOND, MIN(CASE WHEN EventType = 1 THEN eventTime END), MAX(CASE WHEN EventType = 14 THEN eventTime END)) AS TimeDiffInSeconds
FROM netobjectidgroups g
GROUP BY g.netobjectid, g.EventStartCnt
ORDER BY g.netobjectid, g.EventStartCnt
;

It's a variation of the packing intervals problem even though you aren't starting with explicit intervals.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

DesNorton SSC-Insane Points: 24386 More actions · Answer 8

Drew's code can be easily modified to include the extra condition for EventType = 5001. It is still way more efficient than my code

WITH netobjectidgroups AS
(
SELECT *
,CASE WHEN EventType = 1 THEN eventID END AS EventIdOfEventType1
,CASE WHEN EventType = 1 THEN eventTime END AS EventTimeOfEventType1
,CASE WHEN EventType = 14 or EventType = 5001 THEN eventID END AS EventIdOfEventType14
,CASE WHEN EventType = 14 or EventType = 5001 THEN eventTime END AS EventTimeOfEventType14
,SUM(CASE WHEN ec.EventType = 1 THEN 1 ELSE 0 END) OVER(PARTITION BY netobjectid ORDER BY EventTime ROWS UNBOUNDED PRECEDING)  AS EventStartCnt
FROM dbo.tblEventCases AS ec
  where ec.netobjectid = @netObjectId
)
SELECT
g.netobjectid
,MIN(EventIdOfEventType1) AS EventIdOfEventType1
,MIN(EventTimeOfEventType1) AS EventTimeOfEventType1
,MAX(EventIdOfEventType14) AS EventIdOfEventType14
,MAX(EventTimeOfEventType14) AS EventTimeOfEventType14
,DATEDIFF(SECOND, MIN(CASE WHEN EventType = 1 THEN eventTime END), MAX(CASE WHEN EventType = 14 or EventType = 5001 THEN eventTime END)) AS TimeDiffInSeconds
FROM netobjectidgroups g
GROUP BY g.netobjectid, g.EventStartCnt
ORDER BY g.netobjectid, g.EventStartCnt
;

How to post data/code on a forum to get the best help.

Find time different based on Type by making group

Attachments: