Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
Report on Accounts with no active Contracts RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi Guys

    I am trying to write a SQL query (for use in a custom report) to identify all accounts without active contracts

    I have written the query for "show accounts with contracts" 
    SELECT     Account.Name, Contract.CustomerId, Contract.AccountIdName, Contract.StateCode, Contract.StatusCode, Account.new_revenue
FROM         Contract, Account where Account.AccountId = Contract.CustomerId
    and this brings back the accounts with contracts, I stupidly thought by adding a "DOES NOT EQUAL" would bring back the results I wanted.  I have also tried to find all accounts with null valued contracts but again this didn't work.

    Does anyone know how this query should be put together?

    Thanks

    Allan
    http://allanvirtualitbiz.blogspot.com
    Tuesday, November 10, 2009 1:29 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Modify Tanguy's query to use filteredContract and FilteredAccount and it should pull the expected result.

    Tuesday, November 10, 2009 1:45 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Try this 

    SELECT     Account.Name, Contract.CustomerId, Contract.AccountIdName, Contract.StateCode, Contract.StatusCode, Account.new_revenue
FROM         Contract, Account where Account.AccountId not in
(
select accountid from contract where statecode = 1
)
    Assuming 1 is the state code for active

    Just a remark, you shouldn't use Views... Use FilteredView instead
    My blog : http://mscrmtools.blogspot.com You will find: Form Javascript Manager (export/import javascript from forms) ISV.Config Manager (graphical ISV.config edition - export/import) View Layout replicator (customize one view and replicate to others) And others (use tool tag on my blog)
    Tuesday, November 10, 2009 1:47 PM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi, Allan.

    Try this:

    SELECT     
	a.Name
	,a.new_revenue
FROM Account a where not Exists (Select * From contract where customerid = a.accountid)
    This query will return you accounts without existing contracts.
    Truth is opened the prepared mind My blog - http://a33ik.blogspot.com
    Tuesday, November 10, 2009 1:49 PM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote 
    SELECT     Account.Name, Contract.CustomerId, Contract.AccountIdName, Contract.StateCode, Contract.StatusCode, Account.new_revenue
FROM         Account left OUTER JOIN
                      contract ON contract.CustomerId is null
    Thanks donna

    One of the SQL guys here have been able to get this to do what I need

    cheers
    http://allanvirtualitbiz.blogspot.com
    • Marked as answer by AllanConnolly Tuesday, November 10, 2009 1:49 PM
    Tuesday, November 10, 2009 1:49 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Modify Tanguy's query to use filteredContract and FilteredAccount and it should pull the expected result.

    Tuesday, November 10, 2009 1:45 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Try this 

    SELECT     Account.Name, Contract.CustomerId, Contract.AccountIdName, Contract.StateCode, Contract.StatusCode, Account.new_revenue
FROM         Contract, Account where Account.AccountId not in
(
select accountid from contract where statecode = 1
)
    Assuming 1 is the state code for active

    Just a remark, you shouldn't use Views... Use FilteredView instead
    My blog : http://mscrmtools.blogspot.com You will find: Form Javascript Manager (export/import javascript from forms) ISV.Config Manager (graphical ISV.config edition - export/import) View Layout replicator (customize one view and replicate to others) And others (use tool tag on my blog)
    Tuesday, November 10, 2009 1:47 PM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi, Allan.

    Try this:

    SELECT     
	a.Name
	,a.new_revenue
FROM Account a where not Exists (Select * From contract where customerid = a.accountid)
    This query will return you accounts without existing contracts.
    Truth is opened the prepared mind My blog - http://a33ik.blogspot.com
    Tuesday, November 10, 2009 1:49 PM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote 
    SELECT     Account.Name, Contract.CustomerId, Contract.AccountIdName, Contract.StateCode, Contract.StatusCode, Account.new_revenue
FROM         Account left OUTER JOIN
                      contract ON contract.CustomerId is null
    Thanks donna

    One of the SQL guys here have been able to get this to do what I need

    cheers
    http://allanvirtualitbiz.blogspot.com
    • Marked as answer by AllanConnolly Tuesday, November 10, 2009 1:49 PM
    Tuesday, November 10, 2009 1:49 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi Alan, a little cheat I use for this type of thing is to build a marketing list of all the accounts, then remove all the accounts with contracts. This leaves you with a list of accounts without contracts. This method is limited to leads, contacts and accounts, but regular users can try with without executing SQL queries against the raw data.

    Regards, Neil
    Tuesday, November 10, 2009 5:27 PM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thanks Neil

    That is a very sneaky work around, I like it! And thanks to everyone who has also posted, I will try all of the queries and let you know wheich one worked best!

    Thanks

    Allan
    http://allanvirtualitbiz.blogspot.com
    Thursday, November 12, 2009 10:27 AM