Creating a New Login/Logout Agent report

I am trying to create a report definition from scratch using Agent, Agent_Logout, Agent_Interval & Agent Half_Hour tables.

it is a simple report and have been able to create the report definition, however when I run the report for a given date, when I know there is data then I do not get any records come back

Here is the query.

Anyone with any ideas?

SELECT
Agent.EnterpriseName as AgentName,
Agent_Logout.LogoutDateTime,
Agent_Logout.LoginDuration,
Agent_Logout.Extension,
Agent_Logout.ReasonCode,
Agent.SkillTargetID,
Agent_Half_Hour.DateTime,
Agent_Half_Hour.TimeZone,
Agent_Interval.LoggedOnTime

FROM
Agent_Logout
JOIN Agent ON Agent_Logout.SkillTargetID = Agent.SkillTargetID
JOIN Agent_Half_Hour ON Agent_Half_Hour.SkillTargetID = Agent_Logout.SkillTargetID
JOIN Agent_Interval ON Agent_Interval.SkillTargetID = Agent_Half_Hour.SkillTargetID
WHERE  Agent.EnterpriseName
IS NOT NULL ORDER BY Agent.EnterpriseName ASC

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.