At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.
COURSE of the MONTH
8 days, 10 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Self Join to multiple tables MS SQL server
Posted on 2012-12-29
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.sqlservercentra
When I execute the following script the output below is generated.
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
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.sqlservercentra
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
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
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
5
3
10 Comments
Active today
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.
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
Remote Connect
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?
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
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
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?
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
Thanks
Active today
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.
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.
OK. If it cannot be achieved in the way that I require I will run three separate blocks to get what I need.
Active today
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).
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).
Featured Post
Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses
Course of the Month8 days, 10 hours left to enroll
765 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.