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
Microsoft SQL Server
Last Comment
BJM1M
8/22/2022 - Mon
Qlemo
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.
BJM1M
ASKER
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.
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?
BJM1M
ASKER
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.
OK. If it cannot be achieved in the way that I require I will run three separate blocks to get what I need.
Qlemo
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).