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.

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

Archived

This topic is now archived and is closed to further replies.

×
×
  • 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.