July 23, 2002 at 3:39 pm
Hello,
I thought I had this down but some times the data the view returns is not correct or more to the point what I would expect. I have a select statement as follows...
select Login.Date,
Login.AgentName,
Login.Timestamp as 'Login',
(select top 1 timestamp from eAgentLoginStat where agentname = Login.agentname and
eventtype = 'LO' and timestamp > login.timestamp and date = login.date) as 'Logout'
from dbo.eAgentLoginStat as Login
where Login.EventType = 'LI' and login.date = '2002-07-23' and login.agentname = 'gylesa'
group by Login.Date, Login.AgentName, Login.Timestamp
It would get data from a table that looks like this...
Date, EventType, Timestamp, AgentName
23/07/2002, LI, 7:30:31 am, gylesa
23/07/2002, LO, 7:30:33 am, gylesa
23/07/2002, LI, 7:30:35 am, gylesa
23/07/2002, LO, 10:37:37 am, gylesa
23/07/2002, LI, 10:44:13 am, gylesa
23/07/2002, LO, 11:29:58 am, gylesa
23/07/2002, LI, 12:32:06 pm, gylesa
23/07/2002, LO, 2:30:55 pm, gylesa
23/07/2002, LI, 2:45:07 pm, gylesa
23/07/2002, LO, 4:30:06 pm, gylesa
I would expect it to return data like the follwoing...
Date, AgentName, Login, Logout
23/07/2002, gylesa, 7:30:31 am, 7:30:33 am
23/07/2002, gylesa, 7:30:35am, 10:37:37 am
23/07/2002, gylesa, 10:44:13 am, 11:29:58 am
23/07/2002, gylesa, 12:32:06 pm, 2:30:55 pm
23/07/2002, gylesa, 2:45:07 pm, 4:30:06 pm
But it returns the following data instead...
Date, AgentName, Login, Logout
23/07/2002, gylesa, 7:30:31 am, 10:37:37 am
23/07/2002, gylesa, 7:30:35am, 10:37:37 am
23/07/2002, gylesa, 10:44:13 am, 11:29:58 am
23/07/2002, gylesa, 12:32:06 pm, 2:30:55 pm
23/07/2002, gylesa, 2:45:07 pm, 4:30:06 pm
As you might notice the first row logout time does not return the correct time. If anyone could help with this that would be magic.
Many thanks,
Chris
July 23, 2002 at 3:47 pm
Hello there,
Sorry about this. It seems that every time I post here I figure out the answer a few minutes later no matter how long I've been trying to figure out a problem.
I altered my select statement to read the following...
select Login.Date,
Login.AgentName,
Login.Timestamp as 'Login',
(select min(timestamp) from eAgentLoginStat
where agentname = Login.agentname and
eventtype = 'LO' and timestamp > login.timestamp and date = login.date) as 'Logout'
from dbo.eAgentLoginStat as Login
where Login.EventType = 'LI' and login.date = '2002-07-23' and login.agentname = 'gylesa'
group by Login.Date, Login.AgentName, Login.Timestamp
Thanks anyhow, always great to read the issues on this site.
Cheers,
Chris
July 23, 2002 at 4:25 pm
No problem. Posting your question is a lot like explaining it to someone else, gets you really walking through it..and often helps you find the problem without the other person doing anything!
Andy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply