July 17, 2002 at 9:33 pm
Hi there,
I have a table called eAgentLoginStat and here is a quick snapshot of the data...
Date EventID Timestamp AgentName
2002-07-17 LI 2002-07-17 08:01:00 bella
2002-07-17 LO 2002-07-17 09:23:00 bella
2002-07-17 LI 2002-07-17 10:00:24 bella
2002-07-17 LO 2002-07-17 11:40:00 bella
2002-07-17 LI 2002-07-17 12:55:01 bella
2002-07-17 LO 2002-07-17 14:23:45 bella
2002-07-17 LI 2002-07-17 07:34:53 rutherfordc
2002-07-17 LO 2002-07-17 18:01:01 rutherfordc
In the EventID col LI stands for Login and LO stands for Logout. There can be many different AgentNames. An agent can login or out many different times.
This is what I'm trying to get...
Date LoggedIn LoggedOut AgentName
2002-07-17 2002-07-17 08:01:00 2002-07-17 09:23:00 bella
2002-07-17 2002-07-17 10:00:24 2002-07-17 11:40:00 bella
2002-07-17 2002-07-17 12:55:01 2002-07-17 14:23:45 bella
2002-07-17 2002-07-17 07:34:53 2002-07-17 18:01:01 rutherfordc
If anyone knows how to get these results that would be magic. My endstate is to create a view and then have Crystal Reports access it.
Many thanks,
Chris Rutherford
July 17, 2002 at 10:02 pm
You'd need a self join and a subquery.
Join one looks for logins. This joins back to the same table for logouts, but with the same ID. Also, the logout time should be the min logout time above the login time, which is what the subquery should return.
give it a try and post back. We can help you if you get stuck.
Steve Jones
July 17, 2002 at 10:10 pm
Hello,
I've got this view in place at the moment...
SELECT Date,
AgentName,
(SELECT MIN(timestamp) FROM dbo.eagentloginstat
WHERE eventtype = 'LI' AND
date = eAgentLoginStat_Main.date AND
agentname = eAgentLoginStat_Main.agentname) AS LoginTime,
(SELECT MAX(timestamp) FROM dbo.eagentloginstat
WHERE eventtype = 'LO' AND
date = eAgentLoginStat_Main.date AND
agentname = eAgentLoginStat_Main.agentname) AS LogoutTime
FROM dbo.eAgentLoginStat eAgentLoginStat_Main
GROUP BY Date, AgentName
However it only gives me the min timestamp for the LI and the max timestamp for the LO which origionally what I wanted but now we need to see all the LI's and LO's inbetween as well.
I've not used a join as sometimes an agent does not log out and the join that I made does not display any data for that person for that day. Here is that select statement...
select main.date,
main.agentname,
min(main.timestamp) as 'LogginTime',
max(sub.timestamp) as 'LogoutTime'
from dbo.eAgentLoginStat as Main full join dbo.eAgentLoginStat as Sub on
main.agentname = sub.agentname and main.date = sub.date
where main.eventtype = 'LI' and
sub.eventtype = 'LO'
group by main.date, main.agentname
order by main.date asc, main.agentname asc
Hope this helps.
Many thanks,
Chris
July 17, 2002 at 11:58 pm
Hello there.
Well this is how far I got...
select Login.Date,
Login.AgentName
from dbo.eAgentLoginStat as Login inner join dbo.eAgentLoginStat as Logout on
Login.AgentName = Logout.AgentName
where Login.EventType = 'LI' and Logout.EventType = 'LO'
group by Login.Date, Login.AgentName
However I don't know how to "the logout time should be the min logout time above the login time, which is what the subquery should return."
I'm stuck.
Cheers,
Chris
July 18, 2002 at 8:01 pm
Hello,
Well I've got a bit further but I'm still stuck. This is what I have...
select Login.Date,
Login.AgentName,
Login.Timestamp as 'Login',
(select timestamp from eagentloginstat where eventtype = 'LO' and agentname = login.agentname and
timestamp ) as 'Logout'
from dbo.eAgentLoginStat as Login
where Login.EventType = 'LI' and Login.Date = '2002-07-18'
group by login.date, login.agentname, login.timestamp
order by Login.Date asc, Login.AgentName asc
It gives me the date, the agentname and when they logged in but the sub-query part is not working as I still don't know how to "the logout time should be the min logout time above the login time, which is what the subquery should return."
Any help would be much appreciated.
Cheers,
Chris
July 18, 2002 at 8:20 pm
Hello,
Never mind I have figured it out. Thanks Steve for the point in the right direction.
Cheers,
Chris
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply