Query Login Logout Table

[scott.timlock]

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 September 22, 2012 by scott.timlock

[kicken]

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

 

[scott.timlock]

Column 'SRO_VT_LOG.dbo._LogEventChar.CharID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

SELECT s.CharID,  MAX(s.EventTime) as lastLogout

[kicken]

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