Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
Problem Getting SQL Statement to get all my records RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi

    I have written a query see below which kinda works out.

    However, I also need to add to this code so I can get all records...

    where stc.value = 'E - Single'
    CON.AccountIdName is not equal to the companies listed in the query 
    and has null in their CON.AccountIdName field.

    Any help is appreciated - I am really stuck on this one.

    SELECT 
	CON.new_MembershipNumber,
	CON.AccountIdName,
	CON.FirstName,
	CON.LastName,
	STC.Value
	
FROM dbo.Contact CON

LEFT OUTER JOIN StringMap STC
           ON           STC.attributename  = 'new_coveragetype' 
            AND           STC.attributevalue = CON.new_CoverageType
            AND           STC.objecttypecode = 2 
            AND           STC.langid = 1033

WHERE  CON.StatusCode	  = 1

	AND CON.AccountIdName != 'Company 1'
	AND CON.AccountIdName != 'Company 2'
	AND CON.AccountIdName != 'Company 3'
	AND CON.AccountIdName != 'Company 4'
	AND CON.AccountIdName != 'Company 5'
	AND CON.AccountIdName != 'Company 6'

    AND    STC.Value      =  'E - Single'
  
    


select 'the number of rows is:', @@rowcount

    Thursday, July 11, 2013 10:04 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    I believe new_coveragetype is a picklist. Please use the FilteredView for Contact that includes the display label of the picklist fields so you would not have to join the string map table.

    Also you could use the IN operator (Con.AccountIdName != null AND Con.AccountIdName NOT IN ('Company1', 'Company2'))

    Always use filtered views when accessing CRM data tables as it implements the CRM security privileges.

    HTH

    Sam

    Dynamics CRM MVP | Inogic | http://inogic.blogspot.com| news at inogic dot com

    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"

    • Proposed as answer by Sam - Inogic Friday, July 12, 2013 3:53 AM
    Friday, July 12, 2013 3:53 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi

    I added in the "IN" clause however this did not fix anything.
    I need to get the record that match the criteria plus where their Accountidname is null as well

    So I missing 11 record from the database that match that criteria.
    This is what I don't know how to do

    SELECT 
	CON.AccountIdName,
	CON.FirstName,
	CON.LastName,
	STC.Value
	
FROM dbo.Contact CON

LEFT OUTER JOIN StringMap STC
     ON           STC.attributename   = 'new_coveragetype' 
     AND           STC.attributevalue = CON.new_CoverageType
     AND           STC.objecttypecode = 2 
     AND           STC.langid = 1033

WHERE  CON.StatusCode				= 1

	 AND    STC.Value               = 'ES - Couple'
   
	 AND CON.AccountIdName NOT IN
	 (
	 'Capo Industries Ltd.',
	 'Pioneer Energy',
	 'Salerno Dairy Products',
	 'Trackless Vehicles Limited',
	 'YMCA of Greater Toronto',
	 'YMCA of Hamilton/Burlington'
	 )
	 
	 AND CON.AccountIdName IS NULL

    Friday, July 12, 2013 10:10 AM