How are your filter fields built? Or are you just filtering on Agent_Half_Hour.DateTime? Agent_Interval.LoggedOnTime likely won't give you much. I just ran the query directly in SQL management studio and it works fine against the AWDB. LogoutDateTime would probably work well as a filter here.
Also, a couple things you should consider to improve the SQL used here...
- Your joins need more criteria to create sensible rows of data... right now you're going to get exponentially more results than you should need. For example, if you select 1 agent, and they have 10 entries in Agent_Half_Hour, and 10 entries in Agent_Interval, you'll get 100 results instead of 10. Up that to just 100 relevant entries in each table, and you get 10,000 rows back.
- When you can, I would use more specific JOIN statements, like "LEFT JOIN", "RIGHT JOIN", or "INNER JOIN".
- What is the Agent_Half_Hour table being brought in for? It's not providing any useful data in the query, and is only adding extra nonsense rows.
- Agent_Interval.LoggedOnTime provides a duration in seconds, within the current Agent_Interval.DateTime interval. I don't think this is going to provide what you're looking for here. If you're looking for the time that the agent logged on, the simplest way to get that would be to do some SQL time math with Agent_Logout.LogoutDateTime and Agent_Logout.LoginDuration.
So, based on the above, a better query may be something like:
SELECT
A.EnterpriseName as AgentName,
AL.LogoutDateTime,
AL.LoginDuration,
AL.Extension,
AL.ReasonCode,
DATEADD(s,-(AL.LoginDuration),AL.LogoutDateTime) AS LoginDateTime,
A.SkillTargetID
FROM
Agent_Logout AL
JOIN Agent A ON AL.SkillTargetID = A.SkillTargetID
Of course add in any appropriate WHERE, ORDER BY, etc. I would use either LogoutDateTime or LoginDateTime as your time filter, and SkillTargetID as your agent filter.
-Jameson
