Find Empty Salesforce Accounts

gistfile1.sql

SELECT Id, Name, CreatedDate, LastModifiedDate
FROM (
	SELECT Id, Name, CreatedDate, LastModifiedDate
	FROM OPENQUERY(DBAMP, 'select * from Account') 
	WHERE Account_Profile__c IS NULL
	AND Account_Stage__c IS NULL
	AND Account_Tier__c IS NULL
	AND AccountNumber IS NULL
	AND Annual_Revenue_in_millions__c IS NULL
	AND AnnualRevenue IS NULL
	AND Asset_Count__c IS NULL
	AND BillingCity IS NULL
	AND BillingCountry IS NULL
	AND BillingLatitude IS NULL
	AND BillingLongitude IS NULL
	AND BillingPostalCode IS NULL
	AND BillingState IS NULL
	AND BillingStreet IS NULL
	AND Cleaned__c IS NULL
	--AND CleanStatus IS NULL
	--AND CreatedById IS NULL
	--AND CreatedDate IS NULL
	AND CRM__c IS NULL
	AND Customer_Success_Status__c IS NULL
	AND Date_of_Last_Won_Deal__c IS NULL
	AND Description IS NULL
	AND DunsNumber IS NULL
	AND ERP__c IS NULL
	AND Expired_Asset_Count__c IS NULL
	AND Fax IS NULL
	AND FY2014_Potential__c IS NULL
	--AND Id IS NULL
	AND IDE__c IS NULL
	AND Industry IS NULL
	--AND IsDeleted IS NULL
	AND JigsawCompanyId IS NULL
	AND LastActivityDate IS NULL
	--AND LastModifiedById IS NULL
	--AND LastModifiedDate IS NULL
	--AND LastReferencedDate IS NULL
	--AND LastViewedDate IS NULL
	AND LID__LinkedIn_Company_Id__c IS NULL
	AND MasterRecordId IS NULL
	AND MDM_MAM__c IS NULL
	AND NaicsCode IS NULL
	AND NaicsDesc IS NULL
	--AND Name IS NULL
	AND No_of_Developers__c IS NULL
	AND Number_of_Apps_on_Roadmap__c IS NULL
	AND Number_of_Developers__c IS NULL
	AND Number_of_MSDN_seats__c IS NULL
	AND NumberOfEmployees IS NULL
	--AND OwnerId IS NULL
	AND Ownership IS NULL
	AND ParentId IS NULL
	AND Phone IS NULL
	AND Potential__c IS NULL
	AND Primary_Contact__c IS NULL
	--AND Priority__c IS NULL
	--AND qbdialer__Dials__c IS NULL
	AND qbdialer__LastCallTime__c IS NULL
	AND qbdialer__ResponseTime__c IS NULL
	AND Sales_Development_Rep__c IS NULL
	AND SDR_Action__c IS NULL
	AND SDR_Action_Date__c IS NULL
	AND SDR_Response_Date__c IS NULL
	AND SDR_Response_Level__c IS NULL
	AND ShippingCity IS NULL
	AND ShippingCountry IS NULL
	AND ShippingLatitude IS NULL
	AND ShippingLongitude IS NULL
	AND ShippingPostalCode IS NULL
	AND ShippingState IS NULL
	AND ShippingStreet IS NULL
	AND Sic IS NULL
	AND SicDesc IS NULL
	AND Site IS NULL
	--AND SystemModstamp IS NULL
	AND Team__c IS NULL
	AND TickerSymbol IS NULL
	AND Total_Value_of_Won_Opportunities__c = 0.0
	AND Tradestyle IS NULL
	--AND Type IS NULL
	AND Website IS NULL
	AND X1st_App_Launch_Date__c IS NULL
	AND YearStarted  IS NULL
) p1
LEFT OUTER JOIN (select DISTINCT AccountId AS [aId] FROM OPENQUERY(DBAMP, 'select AccountId from Contact')) as t1 on t1.aId = p1.Id
LEFT OUTER JOIN (select DISTINCT AccountId AS [aId] FROM OPENQUERY(DBAMP, 'select AccountId from Asset')) as t2 on t2.aId = p1.Id
LEFT OUTER JOIN (select DISTINCT AccountId AS [aId] FROM OPENQUERY(DBAMP, 'select AccountId from Contract')) as t3 on t3.aId = p1.Id
LEFT OUTER JOIN (select DISTINCT AccountId AS [aId] FROM OPENQUERY(DBAMP, 'select AccountId from Event')) as t4 on t4.aId = p1.Id
LEFT OUTER JOIN (select DISTINCT AccountId AS [aId] FROM OPENQUERY(DBAMP, 'select AccountId from Case')) as t5 on t5.aId = p1.Id
LEFT OUTER JOIN (select DISTINCT AccountId AS [aId] FROM OPENQUERY(DBAMP, 'select AccountId from Task')) as t6 on t6.aId = p1.Id
LEFT OUTER JOIN (select DISTINCT Account__c AS [aId] FROM OPENQUERY(DBAMP, 'select Account__c from Subscription__c')) as t7 on t7.aId = p1.Id
LEFT OUTER JOIN (select DISTINCT AccountId AS [aId] FROM OPENQUERY(DBAMP, 'select AccountId from Opportunity')) as t8 on t8.aId = p1.Id
LEFT OUTER JOIN (select DISTINCT Account__c AS [aId] FROM OPENQUERY(DBAMP, 'select Account__c from Activation__c')) as t9 on t9.aId = p1.Id
LEFT OUTER JOIN (select DISTINCT Deskcom__Account__c AS [aId] FROM OPENQUERY(DBAMP, 'select Deskcom__Account__c from Deskcom__Case__c')) as t10 on t10.aId = p1.Id
LEFT OUTER JOIN (select DISTINCT Account__c AS [aId] FROM OPENQUERY(DBAMP, 'select Account__c from Customer_Apps__c')) as t11 on t11.aId = p1.Id
WHERE t1.aId IS NULL
AND t2.aId IS NULL
AND t3.aId IS NULL
AND t4.aId IS NULL
AND t5.aId IS NULL
AND t6.aId IS NULL
AND t7.aId IS NULL
AND t8.aId IS NULL
AND t9.aId IS NULL
AND t10.aId IS NULL
AND t11.aId IS NULL