How to find record created in Last quarter using custom query

Hi All,

Please suggest on How to find record created in Last quarter using custom query?

Thanks,

Shreya

  • Hi Shreya,

    You can create custom search filters when searching within specific modules.

    For example, if you wanted to find an Account record created last quarter, navigate to the Accounts module and click "Build Filter" next to the search bar. Select "Date Created" from the first drop down selection. In the second drop down section, select the filtering method you think is best. In this case, let's choose "Is Between". In the third drop down, select the appropriate date range. If we are searching for Account records created Q4 2022, you would enter the dates October 1, 2022 and December 31, 2022. 

    Once you have that completed, you will see a list of all records that meet those filter requirements. You can dig further by entering search terms in the search bar (such as a partial name of the account) which will return results to your search term that also match the conditions of the filter.

    If you wish to generate a report that returns all records created within a particular module last quarter, you can use the same type of filter.

    To do this, create a new Rows and Columns report for the target module. Within the "Define Filters" section, select the "Date Created" field. Here, in the report builder, there are more options for the filtering method. You can actually select "Last Quarter", "This Quarter", or "Next Quarter", or you can simply choose "Is Between" and enter your custom date range. 

    I hope this helps! If I can clarify anything or provide any more details let me know.

    Good luck!

    John

  • If you are looking for a query, mysql has formulas to get the first and last day of the quarter

    SELECT
    MAKEDATE(YEAR(CURDATE()), 1) + INTERVAL QUARTER(CURDATE())-2 QUARTER as first_day_of_quarter,
    MAKEDATE(YEAR(CURDATE()), 1) + INTERVAL QUARTER(CURDATE())-1 QUARTER - INTERVAL 1 DAY as last_day_of_quarter

    So, for example, to get all the opportunities from last quarter you can use:

    select *
     from opportunities
    WHERE date(date_entered) <=
      MAKEDATE(YEAR(CURDATE()), 1) + INTERVAL QUARTER(CURDATE())-1 QUARTER - INTERVAL 1 DAY 
      and date(date_entered) >=
      MAKEDATE(YEAR(CURDATE()), 1) + INTERVAL QUARTER(CURDATE())-2 QUARTER 

    FrancescaS