none
How To Find Accounts with no Opportunities associated with it

    Domanda

  • Good Afternoon All,

    I have a requirement to find all accounts with no opportunities and activities associated with it in past 3 months

    Neglected accounts report only taking care of activity part but not opportunities associated and modified with it.

    Is there a way I can achieve this using advance find or report?

    Thank you very much for your help in advance

     

     

    lunedì 28 febbraio 2011 18:44

Risposte

  • You cannot do this with advanced find, as it doesn't support sub-queries. You could write a custom report basing the SQL query around the following:

    select

     

    * from FilteredAccount

    where accountid not in (select customerid from FilteredOpportunity where datediff(d, createdon, GETDATE()) < 90)

    and

     

    accountid not in (select regardingobjectid from FilteredActivityPointer where datediff(d, createdon, GETDATE()) < 90)

    An alternative is to create a marketing list and add all accounts to it, then use Advanced Find to remove the accounts that have an opportunity created in the last 3 months, then do the same for accounts with activities in the last 3 months. The list will then contain what you want


    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk
    lunedì 28 febbraio 2011 19:05
    Moderatore
  • Thanks for your quick response and help David

    Do I need to run this query against CRM database?

    I tried the second option marketing list members it is working well, now I am working on adding required columns for getting required data

     

    lunedì 28 febbraio 2011 19:49

Tutte le risposte

  • You cannot do this with advanced find, as it doesn't support sub-queries. You could write a custom report basing the SQL query around the following:

    select

     

    * from FilteredAccount

    where accountid not in (select customerid from FilteredOpportunity where datediff(d, createdon, GETDATE()) < 90)

    and

     

    accountid not in (select regardingobjectid from FilteredActivityPointer where datediff(d, createdon, GETDATE()) < 90)

    An alternative is to create a marketing list and add all accounts to it, then use Advanced Find to remove the accounts that have an opportunity created in the last 3 months, then do the same for accounts with activities in the last 3 months. The list will then contain what you want


    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk
    lunedì 28 febbraio 2011 19:05
    Moderatore
  • Thanks for your quick response and help David

    Do I need to run this query against CRM database?

    I tried the second option marketing list members it is working well, now I am working on adding required columns for getting required data

     

    lunedì 28 febbraio 2011 19:49