Solved

Self Join to multiple tables MS SQL server

Posted on 2012-12-29
10
440 Views
Last Modified: 2012-12-31
Question:

I need to add additional columns 'Total Locked time' and 'Total Remote Connect' for EventTypeID's 5&6, 7&8 respectively.

I have based the current solution on this article:

http://www.sqlservercentral.com/Forums/Topic693606-8-1.aspx

Background

Using MS SQL server have created the following table to store user login, logout, remote connect, remote disconnect, workstation lock and unlock:

CREATE TABLE [dbo].[DetailedDeviceEvents](
[ID] [bigint]
IDENTITY(1,1) NOT NULL,
[DeviceID] [int] NOT NULL,
[ScheduleID] [bigint]NOT NULL,
[EventTypeID] [int] NOT NULL,
[EventTime] [datetime] NOT NULL,
[CreatedBy] [int] NOT NULL,
[CreatedDateTime] [datetime]NOT NULL,
[RemoteDeviceID] [int] NULL,
[via] [varchar] (50) NULL,
[ntlogin] [varchar] (100) NULL

Open in new window


When I execute the following script the output below is generated.


SELECT
t1.ntlogin ntlogin,
t2.eventTime LogoutTime,
MIN(DateDiff(n, t1.Eventtime, t2.Eventtime)) [LoginDuration(Mins)]
FROM DetailedDeviceEvents t1
INNER join DetailedDeviceEvents t2
ON t1.ntlogin = t2.ntlogin
AND t1.EventTypeID = 3
AND t2.EventTypeID = 4
AND t1.EventTime < t2.EventTime

WHERE t2.EventTime between '2012-11-01' and '2012-12-19'
GROUP BY t1.ntlogin, t2.EventTime

Open in new window


Example output
ntlogin     LogoutTime                          LoginDuration(Mins)
jjones     2012-11-01   13:09:41.450      276
ssmith    2012-11-01   18:28:01.370      547
ssmith    2012-11-02   12:48:43.583      222
ssmith    2012-11-02   16:01:54.837     190
ssmith    2012-11-05   17:06:45.393     489
Xmoanne 2012-11-06   14:31:08.657    306
ssmith    2012-11-06   18:06:09.493     534
tevans    2012-11-07   17:29:11.527      373
ssmith    2012-11-07   17:31:36.327     528
Xmoanne 2012-11-08   14:29:43.597    295
ssmith    2012-11-08   18:48:02.197     638
Xmoanne 2012-11-09 14:28:27.373      289

I could use a UNION statement for the remaining two banks of EventTypeID's (5&6, 7&8) and concatenate the output, but that wouldn't give me the individual column totals.

What I need is a solution that provides the additional two total columns, I believe that this can be accomplished by using additional self referencing tables; I have tried several additional inner joins to additional tables without success. I will be running this on SQL2008 or SQL 2012, so if there are any additional TSQL functions that could make the task easier then feel free to propose. I looking for speed an efficiency as  there will be many thousands of rows. Indexing will need to be added to the 'ntlogin' and date columns in order to improve performance, but in the first instance I require help to solve the problem.

Please let me know if you require additional information.

Thanks
0
Comment
Question by:BJM1M
  • 5
  • 3
10 Comments
 
LVL 69

Expert Comment

by:Qlemo
ID: 38730695
What exactly do you expect? You need common criteria to show aggregated data of different sources (like the event types here). It does not make sense to display a "Logout Time" together with a "Total Locked time" - that is, if you do not mean to display the overall total for the user, which would lead to a repeated value for each logout event.
0
 

Author Comment

by:BJM1M
ID: 38730762
What I expect is:

Example output
ntlogin     LogoutTime                          LoginDuration(Mins) Locked time  Remote time
jjones     2012-11-01   13:09:41.450      276                         123                  55
ssmith    2012-11-01   18:28:01.370      547                         76                     7
ssmith    2012-11-02   12:48:43.583      222                         55                    25

If run the following code - note the change for the EventTypeID's - 5&6 i.e. Lock/Unlock, I receive data as shown above for the Lock/Unlock events, similarly for the Remote Connect/Disconnect.

Lock & Unlock
SELECT
t1.ntlogin ntlogin,
t2.eventTime LogoutTime,
MIN(DateDiff(n, t1.Eventtime, t2.Eventtime)) [LoginDuration(Mins)]
FROM DetailedDeviceEvents t1
INNER join DetailedDeviceEvents t2
ON t1.ntlogin = t2.ntlogin
AND t1.EventTypeID = 5 -- Lock
AND t2.EventTypeID = 6 -- Unlock
AND t1.EventTime < t2.EventTime

WHERE t2.EventTime between '2012-11-01' and '2012-12-19'
GROUP BY t1.ntlogin, t2.EventTime

Open in new window


Remote Connect
SELECT
t1.ntlogin ntlogin,
t2.eventTime LogoutTime,
MIN(DateDiff(n, t1.Eventtime, t2.Eventtime)) [LoginDuration(Mins)]
FROM DetailedDeviceEvents t1
INNER join DetailedDeviceEvents t2
ON t1.ntlogin = t2.ntlogin
AND t1.EventTypeID = 7 -- Remote Connect
AND t2.EventTypeID = 8 -- Remote Disconnect
AND t1.EventTime < t2.EventTime

WHERE t2.EventTime between '2012-11-01' and '2012-12-19'
GROUP BY t1.ntlogin, t2.EventTime

Open in new window


So, what I want is to add the output from the code above to the overall script so as to produce the Lock/Unlock  Remote connection duration for the ntlogin.

Hopefully this answers your question?
0
 

Author Comment

by:BJM1M
ID: 38730766
Just one further point, if by showing the Logout time causes issues, then it would be fine to show the totals for each category, i.e. LoginDuration(Mins), Locked time,  Remote time.

Thanks
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 69

Accepted Solution

by:
Qlemo earned 100 total points
ID: 38730777
The point is, as I said already, you need common criteria to summarize. The logout time will not fit to lock or remote event timestamps, I assume, so we can't match those event types.
Leaving the date aside, we only had the login, and that would allow for a single row per login only.
I understand well that you can get all info isolated from each other - you have a time stamp for each event type, which makes it unique. But putting together related info is difficult. You will need a rule, like the lock events before login, but after prior logout.
0
 

Author Comment

by:BJM1M
ID: 38730806
OK. If it cannot be achieved in the way that I require I will run three separate blocks to get what I need.
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 38731445
Seems as if you did not fully understand how the grading system on EE works.
The "C" grade is inappropriate because the answer given was with best effort and knowledge. "You can't do that" can be a valid answer, and then needs to be graded at least with a "B" (but usually is with an "A" - nothing to improve about the answer).
0
 

Author Comment

by:BJM1M
ID: 38731669
OK so what's the process to re-grade?
0
 

Author Comment

by:BJM1M
ID: 38732378
Yes please. Please reassign to A.

Thanks

apologies for any confusion.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
T-SQL 10 35
Stored Proc - Rewrite 42 60
SQL Log size 3 20
SQLCMD Output to file from xpcmd_shell 6 20
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

830 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