Details about this thread
  • State Not Answered
  • Replies 9 replies
  • Subscribers 324 subscribers
  • Views 2497 views
  • Users 0 members are here

Reports - based on Last Week?

Jessica Linstrot

Hi Team,

I need to run a number of reports every Tuesday for activity the previous week.  When defining the filters, there is no Last Week option.  How can I run the report based on the previous week (Sun - Sat or Mon - Sun)? 

I don't want to use Last 7 Days, because then I miss out on last Monday and incorrectly include yesterday.  

I also don't want to use Last # Days (where # = 8), because this would incorrectly include yesterday.

I really appreciate any assistance you can give on this.

Jessica 

Parents
  • 0

    Hi ,

    I am not sure how you would do that with the reports-builder but you can use Advanced Reports (support.sugarcrm.com/.../index.html) with a custom query:

    Fullscreen
    1
    2
    3
    SELECT
    (curdate() - INTERVAL((WEEKDAY(curdate()))+7) DAY) as monday_last_week.
    (curdate() - INTERVAL((WEEKDAY(curdate()))+1) DAY) as sunday_last_week
    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

    So your report query could look something like:

    Fullscreen
    1
    2
    3
    4
    5
    6
    SELECT * FROM leads
    WHERE
    deleted = 0
    AND date_entered >= (curdate() - INTERVAL((WEEKDAY(curdate()))+7) DAY)
    AND date_entered < (curdate() - INTERVAL((WEEKDAY(curdate()))+1) DAY)
    ORDER BY date_entered ASC
    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

    I used Leads for easy testing in MySQL Workbench since we create leads daily.

    FrancescaS

Reply
  • 0

    Hi ,

    I am not sure how you would do that with the reports-builder but you can use Advanced Reports (support.sugarcrm.com/.../index.html) with a custom query:

    Fullscreen
    1
    2
    3
    SELECT
    (curdate() - INTERVAL((WEEKDAY(curdate()))+7) DAY) as monday_last_week.
    (curdate() - INTERVAL((WEEKDAY(curdate()))+1) DAY) as sunday_last_week
    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

    So your report query could look something like:

    Fullscreen
    1
    2
    3
    4
    5
    6
    SELECT * FROM leads
    WHERE
    deleted = 0
    AND date_entered >= (curdate() - INTERVAL((WEEKDAY(curdate()))+7) DAY)
    AND date_entered < (curdate() - INTERVAL((WEEKDAY(curdate()))+1) DAY)
    ORDER BY date_entered ASC
    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

    I used Leads for easy testing in MySQL Workbench since we create leads daily.

    FrancescaS

Children