Jump to content

Query Login Logout Table


scott.timlock

Recommended Posts

Hello, I have recieved some decent assistant from these forums before, so I am back.

 

I have this query...

 

SELECT l.EventTime, c.CharName16, c.CurLevel, g.ID, g.Name
FROM [sRO_VT_LOG].[dbo].[_LogEventChar] AS l, [sRO_VT_SHARD].[dbo].[_Char] AS c, [sRO_VT_SHARD].[dbo].[_Guild] AS g
WHERE c.CharID = l.CharID AND g.ID = c.GuildID AND l.EventID = 4 AND l.EventTime > (SELECT TOP 1 s.EventTime FROM [sRO_VT_LOG].[dbo].[_LogEventChar] AS s WHERE s.CharID = l.CharID AND s.EventID = 6 ORDER BY s.EventTime DESC)
ORDER BY l.EventTime DESC

 

here is an example from the log table

 

CharID   EventTime   EventID  Data1   Data2   Data3
8572 2012-09-13 13:43:40.757 4 0 1583623721 NULL NULL
8572 2012-09-13 13:44:19.570 6 0 1583623721 NULL NULL

 

Which, while referencing other tables for name data, gives me a list of users currently online... however I cannot for the life of me solve the issue of the query not displaying users who are online but for the first time, never creating the logout row (id 6).

 

Also, Im looking for a seperate query for finding the total time a user has been online by subtracting the login times from the logout times, but I dont know where to start. I know its possible, maybe not exactly how I think it is, and thats the other half of the reason i am here.

Edited by scott.timlock
Link to comment
Share on other sites

Try a query like this one to get the online users list

SELECT 
l.EventTime, c.CharName16, c.CurLevel, g.ID, g.Name
FROM [sRO_VT_LOG].[dbo].[_LogEventChar] AS l
INNER JOIN [sRO_VT_SHARD].[dbo].[_Char] AS c on c.CharID = l.CharID 
INNER JOIN [sRO_VT_SHARD].[dbo].[_Guild] AS g on g.ID = c.GuildID 
LEFT JOIN (
	SELECT s.CharID,  MAX(s.EventTime) as lastLogout
	FROM [sRO_VT_LOG].[dbo].[_LogEventChar] AS s 
	WHERE s.EventID = 6 
) as lastLogout on lastLogout.CharID = l.CharID
WHERE 
l.EventID = 4 
AND l.EventTime > lastLogout.lastLogout
ORDER BY l.EventTime DESC

 

Link to comment
Share on other sites

Forgot the GROUP BY clause

 

SELECT 
l.EventTime, c.CharName16, c.CurLevel, g.ID, g.Name
FROM [sRO_VT_LOG].[dbo].[_LogEventChar] AS l
INNER JOIN [sRO_VT_SHARD].[dbo].[_Char] AS c on c.CharID = l.CharID 
INNER JOIN [sRO_VT_SHARD].[dbo].[_Guild] AS g on g.ID = c.GuildID 
LEFT JOIN (
       SELECT s.CharID,  MAX(s.EventTime) as lastLogout
       FROM [sRO_VT_LOG].[dbo].[_LogEventChar] AS s 
       WHERE s.EventID = 6 
       GROUP BY s.CharID
) as lastLogout on lastLogout.CharID = l.CharID
WHERE 
   l.EventID = 4 
   AND l.EventTime > lastLogout.lastLogout
ORDER BY l.EventTime DESC

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.