Getting datetime from group with 'Summary only'

[roberteliasson]

Hi everyone,

I´m trying to add timestamps from where the Agents have logged in and when they logged out.

It works fine if I´m not grouping the figures, but I can´t get any timestamp if I group the figures and use 'Summary only'

Any idea on how I can do this? Since Im having most of my calculations in the Footers.

Here's the bit where I gather the time logged in and time logged out.

LEFT OUTER JOIN (SELECT

SkillTargetID,

LogoutDateTime,

LoginDateTime = DATEADD(s,-(LoginDuration),LogoutDateTime)

FROM Agent_Logout

GROUP BY LogoutDateTime, LoginDuration, SkillTargetID) LoginLogout

ON LoginLogout.LogoutDateTime >= ASGHH.DateTime AND LoginLogout.LogoutDateTime < DATEADD(day, 1, ASGHH.DateTime) AND LoginLogout.SkillTargetID=ASGHH.SkillTargetID

And here's a screenshot of the footer formula which I don´t know what to do with.

[jacparke]

You can apply some aggregate functions at the footer level like SUM(${LoginDateTime}) - although that would make no sense, you would need to summarize a duration or interval like SUM(${LogoutDateTime}-{LoginDateTime}).

Regards,

Jack Parker

[roberteliasson]

Thanks for the reply.

Instead of grouping, I´ve decided not to group on any columns.

But I have one calculation which behaves really strange.

The three calculation is the following:

(SUM(ISNULL(AED.Breaks, 0)) + SUM(ISNULL(AED.LunchTime, 0)) + SUM(ISNULL(AED.OutboundCall, 0)) + SUM(ISNULL(AED.Administration, 0)) + SUM(ISNULL(AED.AfterCallWork, 0)) + SUM(ISNULL(AI.AvailTime,0)) + SUM(ISNULL(DirectCalls.TalkTime, 0)) + SUM(ISNULL(DirectCalls.HoldTime, 0)) + SUM(ISNULL(ASGHH.TalkInTime,0)) + SUM(ISNULL(ASGHH.TalkOutTime, 0)) + SUM(ISNULL(ASGHH.HoldTime,0)) + SUM(ISNULL(ASGHH.ReservedStateTime,0))) as ReadyTime,

SUM(ISNULL(AI.LoggedOnTime, 0)) as LoggedOnTime,

(SUM(ISNULL(AED.Breaks, 0)) + SUM(ISNULL(AED.LunchTime, 0)) + SUM(ISNULL(AED.OutboundCall, 0)) + SUM(ISNULL(AED.Administration, 0)) + SUM(ISNULL(AED.AfterCallWork, 0)) + SUM(ISNULL(AI.AvailTime,0)) + SUM(ISNULL(DirectCalls.TalkTime, 0)) + SUM(ISNULL(DirectCalls.HoldTime, 0)) + SUM(ISNULL(ASGHH.TalkInTime,0)) + SUM(ISNULL(ASGHH.TalkOutTime, 0)) + SUM(ISNULL(ASGHH.HoldTime,0)) + SUM(ISNULL(ASGHH.ReservedStateTime,0))) / SUM(ISNULL(AI.LoggedOnTime, 0)) as perReadyNew,

So ReadyTime is diveded with LoggedOnTime. In the printscreehbelow you can see that the first row has 13048 seconds Ready Time, and 13500 seconds Logged On time. This should give 96,65% on perReadyNew.

But it just doesn´t work when doing the calculation in the query

It works well if I do the very same calculation in the footer fields instead..

Any ideas+

[jacparke · Accepted solution]

Multiply the numerator by 1.0

SQL treats the whole thing as integer math, multiplying by a decimal will force it into treating it as decimal.

(SUM(ISNULL(AED.Breaks, 0)) + SUM(ISNULL(AED.LunchTime, 0)) + SUM(ISNULL(AED.OutboundCall, 0)) + SUM(ISNULL(AED.Administration, 0)) + SUM(ISNULL(AED.AfterCallWork, 0)) + SUM(ISNULL(AI.AvailTime,0)) + SUM(ISNULL(DirectCalls.TalkTime, 0)) + SUM(ISNULL(DirectCalls.HoldTime, 0)) + SUM(ISNULL(ASGHH.TalkInTime,0)) + SUM(ISNULL(ASGHH.TalkOutTime, 0)) + SUM(ISNULL(ASGHH.HoldTime,0)) + SUM(ISNULL(ASGHH.ReservedStateTime,0)) * 1.0) / SUM(ISNULL(AI.LoggedOnTime, 0)) as perReadyNew,

Regards,

Jack Parker

[roberteliasson]

That made the trick! Many thanks Jack!