T-Sql Query Logic.

Question

Post reply

T-Sql Query Logic.

Mr. Kapsicum

SSCertifiable

Points: 6128
More actions
March 9, 2017 at 6:25 am

#328184

Hi All
CREATE TABLE [dbo].[Kapil_Test_Table] ( [Userid] [varchar](50) NULL, [EventTypeName] [varchar](200) NULL, [EventStartTime] [datetime] NULL, [RN] [int] NULL ) ON [PRIMARY] GO
INSERT [dbo].[Kapil_Test_Table] ([Userid], [EventTypeName], [EventStartTime], [RN]) VALUES (N'4341', N'EsxEventTypeLogin', CAST(N'2017-02-24 01:38:04.000' AS DateTime), 1) INSERT [dbo].[Kapil_Test_Table] ([Userid], [EventTypeName], [EventStartTime], [RN]) VALUES (N'4341', N'EsxEventTypeDashboardSelected', CAST(N'2017-02-24 01:38:06.000' AS DateTime), 2) INSERT [dbo].[Kapil_Test_Table] ([Userid], [EventTypeName], [EventStartTime], [RN]) VALUES (N'4341', N'EsxEventTypeScreenSelected', CAST(N'2017-02-24 01:38:12.000' AS DateTime), 3) INSERT [dbo].[Kapil_Test_Table] ([Userid], [EventTypeName], [EventStartTime], [RN]) VALUES (N'4341', N'EsxEventTypeSuiteSelected', CAST(N'2017-02-24 01:38:18.000' AS DateTime), 4) INSERT [dbo].[Kapil_Test_Table] ([Userid], [EventTypeName], [EventStartTime], [RN]) VALUES (N'4341', N'EsxEventTypeScreenSelected', CAST(N'2017-02-24 01:38:23.000' AS DateTime), 5) INSERT [dbo].[Kapil_Test_Table] ([Userid], [EventTypeName], [EventStartTime], [RN]) VALUES (N'4341', N'EsxEventTypeScreenSelected', CAST(N'2017-02-24 01:39:15.000' AS DateTime), 6) INSERT [dbo].[Kapil_Test_Table] ([Userid], [EventTypeName], [EventStartTime], [RN]) VALUES (N'4341', N'EsxEventTypeSuiteSelected', CAST(N'2017-02-24 01:39:43.000' AS DateTime), 7) INSERT [dbo].[Kapil_Test_Table] ([Userid], [EventTypeName], [EventStartTime], [RN]) VALUES (N'4341', N'EsxEventTypeScreenSelected', CAST(N'2017-02-24 01:39:47.000' AS DateTime), 8) INSERT [dbo].[Kapil_Test_Table] ([Userid], [EventTypeName], [EventStartTime], [RN]) VALUES (N'4341', N'EsxEventTypeScreenSelected', CAST(N'2017-02-24 01:39:57.000' AS DateTime), 9) INSERT [dbo].[Kapil_Test_Table] ([Userid], [EventTypeName], [EventStartTime], [RN]) VALUES (N'4341', N'EsxEventTypeAppWentToBackground', CAST(N'2017-02-24 01:40:34.000' AS DateTime), 10) INSERT [dbo].[Kapil_Test_Table] ([Userid], [EventTypeName], [EventStartTime], [RN]) VALUES (N'4341', N'EsxEventTypeAppEnterForeground', CAST(N'2017-02-24 10:06:01.000' AS DateTime), 11) INSERT [dbo].[Kapil_Test_Table] ([Userid], [EventTypeName], [EventStartTime], [RN]) VALUES (N'4341', N'EsxEventTypeSuiteSelected', CAST(N'2017-02-24 10:06:04.000' AS DateTime), 12) INSERT [dbo].[Kapil_Test_Table] ([Userid], [EventTypeName], [EventStartTime], [RN]) VALUES (N'4341', N'EsxEventTypeScreenSelected', CAST(N'2017-02-24 10:06:04.000' AS DateTime), 13) INSERT [dbo].[Kapil_Test_Table] ([Userid], [EventTypeName], [EventStartTime], [RN]) VALUES (N'4341', N'EsxEventTypeLogin', CAST(N'2017-02-24 10:06:17.000' AS DateTime), 14) INSERT [dbo].[Kapil_Test_Table] ([Userid], [EventTypeName], [EventStartTime], [RN]) VALUES (N'4341', N'EsxEventTypeSuiteSelected', CAST(N'2017-02-24 10:06:24.000' AS DateTime), 15) INSERT [dbo].[Kapil_Test_Table] ([Userid], [EventTypeName], [EventStartTime], [RN]) VALUES (N'4341', N'EsxEventTypeScreenSelected', CAST(N'2017-02-24 10:06:24.000' AS DateTime), 16) INSERT [dbo].[Kapil_Test_Table] ([Userid], [EventTypeName], [EventStartTime], [RN]) VALUES (N'4341', N'EsxEventTypeScreenSelected', CAST(N'2017-02-24 10:06:55.000' AS DateTime), 17) INSERT [dbo].[Kapil_Test_Table] ([Userid], [EventTypeName], [EventStartTime], [RN]) VALUES (N'4341', N'EsxEventTypeAppWentToBackground', CAST(N'2017-02-24 10:07:59.000' AS DateTime), 18) INSERT [dbo].[Kapil_Test_Table] ([Userid], [EventTypeName], [EventStartTime], [RN]) VALUES (N'4341', N'EsxEventTypeAppEnterForeground', CAST(N'2017-02-27 14:04:20.000' AS DateTime), 19) INSERT [dbo].[Kapil_Test_Table] ([Userid], [EventTypeName], [EventStartTime], [RN]) VALUES (N'4341', N'EsxEventTypeSuiteSelected', CAST(N'2017-02-27 14:04:34.000' AS DateTime), 20) INSERT [dbo].[Kapil_Test_Table] ([Userid], [EventTypeName], [EventStartTime], [RN]) VALUES (N'4341', N'EsxEventTypeScreenSelected', CAST(N'2017-02-27 14:04:34.000' AS DateTime), 21) INSERT [dbo].[Kapil_Test_Table] ([Userid], [EventTypeName], [EventStartTime], [RN]) VALUES (N'4341', N'EsxEventTypeSuiteSelected', CAST(N'2017-02-27 14:04:38.000' AS DateTime), 22) INSERT [dbo].[Kapil_Test_Table] ([Userid], [EventTypeName], [EventStartTime], [RN]) VALUES (N'4341', N'EsxEventTypeScreenSelected', CAST(N'2017-02-27 14:04:38.000' AS DateTime), 23) INSERT [dbo].[Kapil_Test_Table] ([Userid], [EventTypeName], [EventStartTime], [RN]) VALUES (N'4341', N'EsxEventTypeLogin', CAST(N'2017-02-27 14:05:32.000' AS DateTime), 24) INSERT [dbo].[Kapil_Test_Table] ([Userid], [EventTypeName], [EventStartTime], [RN]) VALUES (N'4341', N'EsxEventTypeAppWentToBackground', CAST(N'2017-02-27 14:05:45.000' AS DateTime), 25) INSERT [dbo].[Kapil_Test_Table] ([Userid], [EventTypeName], [EventStartTime], [RN]) VALUES (N'4341', N'EsxEventTypeAppEnterForeground', CAST(N'2017-02-28 13:52:28.000' AS DateTime), 26) INSERT [dbo].[Kapil_Test_Table] ([Userid], [EventTypeName], [EventStartTime], [RN]) VALUES (N'4341', N'EsxEventTypeScreenSelected', CAST(N'2017-02-28 13:52:28.000' AS DateTime), 27) INSERT [dbo].[Kapil_Test_Table] ([Userid], [EventTypeName], [EventStartTime], [RN]) VALUES (N'4341', N'EsxEventTypeScreenSelected', CAST(N'2017-02-28 13:52:28.000' AS DateTime), 28) INSERT [dbo].[Kapil_Test_Table] ([Userid], [EventTypeName], [EventStartTime], [RN]) VALUES (N'4341', N'EsxEventTypeDashboardSelected', CAST(N'2017-02-28 13:52:31.000' AS DateTime), 29) INSERT [dbo].[Kapil_Test_Table] ([Userid], [EventTypeName], [EventStartTime], [RN]) VALUES (N'4341', N'EsxEventTypeScreenSelected', CAST(N'2017-02-28 13:52:31.000' AS DateTime), 30) INSERT [dbo].[Kapil_Test_Table] ([Userid], [EventTypeName], [EventStartTime], [RN]) VALUES (N'4341', N'EsxEventTypeSuiteSelected', CAST(N'2017-02-28 13:52:34.000' AS DateTime), 31) INSERT [dbo].[Kapil_Test_Table] ([Userid], [EventTypeName], [EventStartTime], [RN]) VALUES (N'4341', N'EsxEventTypeScreenSelected', CAST(N'2017-02-28 13:52:34.000' AS DateTime), 32) INSERT [dbo].[Kapil_Test_Table] ([Userid], [EventTypeName], [EventStartTime], [RN]) VALUES (N'4341', N'EsxEventTypeAppWentToBackground', CAST(N'2017-02-28 13:53:03.000' AS DateTime), 33) INSERT [dbo].[Kapil_Test_Table] ([Userid], [EventTypeName], [EventStartTime], [RN]) VALUES (N'4341', N'EsxEventTypeDashboardSelected', CAST(N'2017-03-02 08:31:06.000' AS DateTime), 34) INSERT [dbo].[Kapil_Test_Table] ([Userid], [EventTypeName], [EventStartTime], [RN]) VALUES (N'4341', N'EsxEventTypeScreenSelected', CAST(N'2017-03-02 08:31:06.000' AS DateTime), 35) INSERT [dbo].[Kapil_Test_Table] ([Userid], [EventTypeName], [EventStartTime], [RN]) VALUES (N'4341', N'EsxEventTypeSuiteSelected', CAST(N'2017-03-02 08:31:10.000' AS DateTime), 36) INSERT [dbo].[Kapil_Test_Table] ([Userid], [EventTypeName], [EventStartTime], [RN]) VALUES (N'4341', N'EsxEventTypeLogin', CAST(N'2017-03-02 08:31:27.000' AS DateTime), 37) INSERT [dbo].[Kapil_Test_Table] ([Userid], [EventTypeName], [EventStartTime], [RN]) VALUES (N'4341', N'EsxEventTypeSuiteSelected', CAST(N'2017-03-02 08:31:27.000' AS DateTime), 38) INSERT [dbo].[Kapil_Test_Table] ([Userid], [EventTypeName], [EventStartTime], [RN]) VALUES (N'4341', N'EsxEventTypeDashboardSelected', CAST(N'2017-03-02 08:31:27.000' AS DateTime), 39) INSERT [dbo].[Kapil_Test_Table] ([Userid], [EventTypeName], [EventStartTime], [RN]) VALUES (N'4341', N'EsxEventTypeScreenSelected', CAST(N'2017-03-02 08:31:27.000' AS DateTime), 40) INSERT [dbo].[Kapil_Test_Table] ([Userid], [EventTypeName], [EventStartTime], [RN]) VALUES (N'4341', N'EsxEventTypeScreenSelected', CAST(N'2017-03-02 08:31:27.000' AS DateTime), 41) INSERT [dbo].[Kapil_Test_Table] ([Userid], [EventTypeName], [EventStartTime], [RN]) VALUES (N'4341', N'EsxEventTypeSuiteSelected', CAST(N'2017-03-02 08:31:31.000' AS DateTime), 42) INSERT [dbo].[Kapil_Test_Table] ([Userid], [EventTypeName], [EventStartTime], [RN]) VALUES (N'4341', N'EsxEventTypeScreenSelected', CAST(N'2017-03-02 08:31:31.000' AS DateTime), 43) INSERT [dbo].[Kapil_Test_Table] ([Userid], [EventTypeName], [EventStartTime], [RN]) VALUES (N'4341', N'EsxEventTypeAppWentToBackground', CAST(N'2017-03-02 08:31:48.000' AS DateTime), 44) INSERT [dbo].[Kapil_Test_Table] ([Userid], [EventTypeName], [EventStartTime], [RN]) VALUES (N'4341', N'EsxEventTypeAppEnterForeground', CAST(N'2017-03-02 09:03:25.000' AS DateTime), 45) INSERT [dbo].[Kapil_Test_Table] ([Userid], [EventTypeName], [EventStartTime], [RN]) VALUES (N'4341', N'EsxEventTypeAppWentToBackground', CAST(N'2017-03-02 09:03:27.000' AS DateTime), 46) INSERT [dbo].[Kapil_Test_Table] ([Userid], [EventTypeName], [EventStartTime], [RN]) VALUES (N'4341', N'EsxEventTypeDashboardSelected', CAST(N'2017-03-08 00:56:10.000' AS DateTime), 47) INSERT [dbo].[Kapil_Test_Table] ([Userid], [EventTypeName], [EventStartTime], [RN]) VALUES (N'4341', N'EsxEventTypeScreenSelected', CAST(N'2017-03-08 00:56:10.000' AS DateTime), 48)
Above is the Schema and Data for one user only.( Query will run for all the users in table.)
Column RN is the Row_number generated based on ORDER BY EventStarttime ASC.

I am attaching 2 images, my issue is that i want to calculate Difference in time for a different partition marked in different colors for a particular user.
Break point for a partition are these particular values i.e EsxEventTypeAppWentToBackground, EsxEventTypeAppTerminated or EsxEventTypeLogout which ever comes first in order. ( Current example has EsxEventTypeAppWentToBackground only but any of above mentioned three values will decide break point).
For e.g Partition will start for RN=1 and will be end at RN=10 , difference in time will calculated 2017-02-24 01:40:34.000 - 2017-02-24 01:40:34.0002017-02-24 01:38:04.000. Next calculation will start for RN=11 and end at RN=18 ( since from 11 to 18 EsxEventTypeAppWentToBackground is the first one to come endpoint will be this but EsxEventTypeAppWentToBackground, EsxEventTypeAppTerminated or EsxEventTypeLogout first one to come will be the endpoint).
By this i want to calculate active usage for a user.
Hope i have explained by problem well, please let me know if there is any confusion or any additional information i have to provide.
Thanks in advance.

Viewing 6 posts - 1 through 5 (of 5 total)

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

heb1014 Hall of Fame Points: 3790 More actions · Answer 1

tough with set-based logic...thinking about it.

would be easy with CLR.

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 2

See if this works for you

WITH CTE1 AS ( SELECT Userid,EventTypeName,EventStartTime,RN, CASE WHEN LAG(EventTypeName) OVER(PARTITION BY Userid ORDER BY RN) IN ('EsxEventTypeAppWentToBackground','EsxEventTypeAppTerminated','EsxEventTypeLogout') THEN 1 ELSE 0 END AS IsStart FROM dbo.Kapil_Test_Table ), CTE2 AS ( SELECT Userid,EventTypeName,EventStartTime,RN, SUM(IsStart) OVER(ORDER BY RN) AS Grp FROM CTE1) SELECT Userid,EventTypeName,EventStartTime,RN,Grp, DATEDIFF(SECOND, MIN(EventStartTime) OVER(PARTITION BY Userid,Grp), MAX(EventStartTime) OVER(PARTITION BY Userid,GRP)) AS TimeDiff FROM CTE2 ORDER BY RN;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

This works for your sample data. Make sure it works for more data and different scenarios.
The CROSS APPLY is there to prevent repeating the same condition several times.
SELECT *, CASE WHEN x.BreakPoint = 1 THEN ROW_NUMBER() OVER(PARTITION BY UserId, x.BreakPoint ORDER BY RN) ELSE ROW_NUMBER() OVER(PARTITION BY UserId ORDER BY RN) - ROW_NUMBER() OVER(PARTITION BY UserId, x.BreakPoint ORDER BY RN) + 1 END FROM Kapil_Test_Table CROSS APPLY( SELECT CASE WHEN EventTypeName IN('EsxEventTypeAppWentToBackground', 'EsxEventTypeAppTerminated', 'EsxEventTypeLogout') THEN 1 ELSE 0 END AS BreakPoint)x ORDER BY RN;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

heb1014 Hall of Fame Points: 3790 More actions · Answer 4

You all are quick!
If someone hasn't already shown the correct answer, check this out. It's not complete because I'm out of time, but it's a start.

First, I'd add an identity column to your table

CREATE TABLE [dbo].[Kapil_Test_Table] ( RowId bigint identity (1,1) not null primary key, [Userid] [varchar](50) NULL, [EventTypeName] [varchar](200) NULL, [EventStartTime] [datetime] NULL, [RN] [int] NULL ) ON [PRIMARY] GO
Then, here is what I have started
with PartitionBreakPoints as ( select * from dbo.[Kapil_Test_Table] where EventTypeName in ('EsxEventTypeAppWentToBackground', 'EsxEventTypeAppTerminated', 'EsxEventTypeLogout') ) select * from ( select t.*, b.EventTypeName as BreakEventTypeName, b.EventStartTime as BreakEventStartTime, b.rowid as BreakRowId, ROW_NUMBER() over (partition by t.RowId order by b.EventStartTime desc) as RowNum from dbo.Kapil_Test_Table t left join PartitionBreakPoints b on b.Userid = t.Userid and b.EventStartTime <= t.EventStartTime ) a where a.RowNum = 1

Mr. Kapsicum SSCertifiable Points: 6128 More actions · Answer 5

Thanks everyone for quick response.
I am analyzing all the solutions provided by you guys for different users and cases. Will get back to you in case of any issues.

Again, Thank you ver much.

Regards