2 lines per agent on Weekly Summary report!

Version 1
    This document was generated from CDN thread

    Created by: Sarah Barnes on 20-02-2013 10:52:21 AM
    I have been running the Agent Weekly summary report, which I had downloaded from the site. When running the report for 11th - 17th Feb, (Monday - Sunday) on some agents I get 2 lines of data... When I then run a daily report for the same time for these agents, I see that the 'extra day' is the Sunday!   If I then run the same weekly report from 10th - 16th Feb (Sunday - Saturday) the same agents are still shoing 2 lines and teh 2nd line is teh Sunday! the SQL is as follows   dateadd(week, datediff(week,0, ASGHH.DateTime), 0) AS Interval  having chaned the 2nd 0, all that does in cahnge the date shown on the Interval Column from a Monday to Sunday if I use -1.. Having changed the 1st 0, upwards or downwards,  I see no difference. Can anyone shed any light on this? Has anyone else used this report and had the same problem and resolved it? Any help anyone can give will be greatly apprecaited. Thanks Sarah        

    Subject: RE: 2 lines per agent on Weekly Summary report!
    Replied by: Sarah Barnes on 20-02-2013 06:26:28 PM
    I have tried it all ways, I have run the reportas an absolute week  2/10 - 2/16 and I still get the xtra day which is the 2/17!
    That does make sense re the Week number on the absolute date range report . On the relative date report however, I think there may still be a problem..
    I will try again and see if I can get it to work. :-)
     

    Subject: RE: 2 lines per agent on Weekly Summary report!
    Replied by: Paige Delk on 20-02-2013 06:01:57 PM
    What I think is happening here is that the sql is grouping by Week Number and the SQL Server starts ther week on Sunday and goes through Saturday.  so if you run the report to cover a Sunday, you'll automatically get a 2nd row. 
    Consider the dates  2/10 and 2/17 are different week numbers so if I selected a begin/end period of 2/11 through 2/17, I'll get one row for the week of 2/10 and one row for the week of 2/17 (the sunday that falls in the next week. 
    Try running your reports for dates that are encompased in a single week (Sunday to Saturday) and see if you still get 2 rows. 
    I'm SO glad you're getting involved in reporting.  There are not enough people that know it!

    Subject: Re: New Message from Sarah Barnes in Contact Center Reporting (CCR) - Conta
    Replied by: Paige Delk on 20-02-2013 06:30:14 PM
    I seem to remember an issue with this some time ago.  Maybe it's in the bug toolkit (if that still exists) - sorry I couldn't help further!

    Sent from my iPhone, please excuse brevity and mistakes.

    On Feb 20, 2013, at 7:27 PM, "Cisco Developer Community Forums" <cdicuser@developer.cisco.com<mailto:cdicuser@developer.cisco.com>> wrote:

    Sarah Barnes has created a new message in the forum "Contact Center Reporting": -------------------------------------------------------------- I have tried it all ways, I have run the reportas an absolute week  2/10 - 2/16 and I still get the xtra day which is the 2/17!
    That does make sense re the Week number on the absolute date range report . On the relative date report however, I think there may still be a problem..
    I will try again and see if I can get it to work. :-)

    --
    To respond to this post, please click the following link: http://developer.cisco.com/web/ccr/community/-/message_boards/view_message/12191905 or simply reply to this email.

    Subject: RE: 2 lines per agent on Weekly Summary report!
    Replied by: Mike Browne on 21-02-2013 02:09:59 AM
    The underlying report sql includes a few extra fields that were provided to make it easier to link reports together via drilldown links, week is one of them.
    In order to include these fields in the report, they are included in the group by clause and unfortunately can cause a split in a row of data.
    To remove this condition, remove the week field from the underlying sql query, the field is not critical to the report.

    Subject: RE: 2 lines per agent on Weekly Summary report!
    Replied by: Sarah Barnes on 21-02-2013 06:24:16 AM
    Hi Mike, I tried this and it did not work. 
    This is only happening on the relative date report you helped us with. From what I understand with what paige has said. The relative date is running Monday to Sunday however there is conflict with underlying information is pulling across Week 7 & 8. What would be better was is we could have chane the relative date to SUnday to Saturday, so it mirrors the Information as Week 7 only.. 
    I have been trying to Manipulate the Relative date SQl, however as you can imagine, I have not been succesful. 
    Anything you can recommend would be welcome. 

    Subject: RE: New Message from Sarah Barnes in Contact Center Reporting (CCR) - Conta
    Replied by: Paige Delk on 21-02-2013 07:16:14 AM
    Sarah, try removing this from your Group By statement:



    &#13;
    dateadd(day, datediff(day,1, ASGHH.DateTime), 0) )



    Paige Delk | 813-835-1306 (o) | 813-486-9885 (c)
    www.aceyus.com<http://www.aceyus.com/>

    ________________________________
    From: Cisco Developer Community Forums [cdicuser@developer.cisco.com]
    Sent: Thursday, February 21, 2013 7:24 AM
    To: cdicuser@developer.cisco.com
    Subject: New Message from Sarah Barnes in Contact Center Reporting (CCR) - Contact Center Reporting: RE: 2 lines per agent on Weekly Summary report!

    Sarah Barnes has created a new message in the forum "Contact Center Reporting": -------------------------------------------------------------- Hi Mike, I tried this and it did not work.
    This is only happening on the relative date report you helped us with. From what I understand with what paige has said. The relative date is running Monday to Sunday however there is conflict with underlying information is pulling across Week 7 & 8. What would be better was is we could have chane the relative date to SUnday to Saturday, so it mirrors the Information as Week 7 only..
    I have been trying to Manipulate the Relative date SQl, however as you can imagine, I have not been succesful.
    Anything you can recommend would be welcome.
    --
    To respond to this post, please click the following link: http://developer.cisco.com/web/ccr/community/-/message_boards/view_message/12210022 or simply reply to this email.

    Subject: RE: 2 lines per agent on Weekly Summary report!
    Replied by: Sarah Barnes on 21-02-2013 08:00:18 AM
    Hi Paige, I am not sure what I am doing wrong, However I have tried what you advised and I still can't  get it to work.
    here is the group by statement
    GROUP BY
     Agent.SkillTargetID,
    Person.LastName,
    Person.FirstName,
     Media_Routing_Domain.EnterpriseName,
    DATEPART(yy, ASGHH.DateTime) ,
    DATEPART(mm, ASGHH.DateTime) ,
    DATEPART(ww, ASGHH.DateTime) ,
    dateadd(week, datediff(week,0, ASGHH.DateTime), -1)
    However further up in the query I have
    FROM     (SELECT
    Media_Routing_Domain.EnterpriseName AS Media, dateadd(week, datediff(week,0, ASGHH.DateTime), -1) AS Interval,
    DATEPART(yy, ASGHH.DateTime) AS Year,
    DATEPART(mm, ASGHH.DateTime) AS Month,
    DATEPART(ww, ASGHH.DateTime) AS Week,
    If i take out the group by, then it errors and I have to take out the From piece, whatever happend the annoymous block doesn't create!
    This is the relative date sql that Mike created and inserted into the report.
    DECLARE @dtStartDateTime DATETIME, @dtEndDateTime DATETIME
    SET @dtStartDateTime = CASE :relative_date
     WHEN 1 THEN dateadd(day, datediff(day, 0, getdate()), 0)
     WHEN 2 THEN dateadd(day, datediff(day, 0, getdate()) - 1, 0)
     WHEN 3 THEN dateadd(week, datediff(week, 0, getdate()), 0)
     WHEN 4 THEN dateadd(week, datediff(week, 0, getdate()) - 1, 0)
     WHEN 5 THEN dateadd(month, datediff(month, 0, getdate()), 0)
     WHEN 6 THEN dateadd(month, datediff(month, 0, getdate()) - 1, 0)
     WHEN 7 THEN dateadd(year,datediff(year, 0, getdate()), 0)
     WHEN 8 THEN dateadd(year,datediff(year, 0, getdate()) - 1, 0)
    END
    SET @dtEndDateTime =  CASE :relative_date
     WHEN 1 THEN dateadd(day, datediff(day, 0, getdate()) - 1, 0)
     WHEN 2 THEN dateadd(day, datediff(day, 0, getdate()) , 0)
     WHEN 3 THEN dateadd(day, datediff(day, 0, getdate()) - 1, 0)
     WHEN 4 THEN dateadd(week, datediff(week, 0, getdate()), 0)
     WHEN 5 THEN dateadd(day, datediff(day, 0, getdate()) - 1, 0)
     WHEN 6 THEN dateadd(month, datediff(month, 0, getdate()), 0)
     WHEN 7 THEN dateadd(day, datediff(day, 0, getdate()) - 1, 0)
     WHEN 8 THEN dateadd(year,datediff(year, 0, getdate()), 0)
    END
    Hope this helps  :-)
     
     
     
     

    Subject: RE: 2 lines per agent on Weekly Summary report!
    Replied by: Sarah Barnes on 21-02-2013 08:12:28 AM
    Hopefully When he comes online it will be sorted.
    Thanks for your Advice on the Hold Question, Everything is Adding up correctly, as far as i  can see... waiting to hear back from the customer.
    Take Care.
    :-)
     

    Subject: RE: 2 lines per agent on Weekly Summary report!
    Replied by: Paige Delk on 21-02-2013 08:05:16 AM
    Mike is a genius, so I will let him guide you from here - it is a strange thing though.  Sorry I couldnt help more.  It is really bugging me though.