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:
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](
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.
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
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.