gistfile1.sql

SELECT Who_FirstName, Who_LastName, ll.Email, (SUM(VSWinCount) + SUM(XSWinCount) + SUM(XSMacCount)) AS Activity
FROM OPENQUERY(DBAMP, 'SELECT Who.FirstName, Who.LastName, WhoId, Subject FROM Task WHERE Subject LIKE ''Started Trial:%'' AND ActivityDate > 2013-08-07 ') AS tt
INNER JOIN OPENQUERY(DBAMP, 'SELECT Id, Email FROM Lead') ll ON tt.WhoId = ll.Id
INNER JOIN OPENQUERY(STOREREPLICA, 'SELECT * FROM StoreReplica.dbo.Nop_Customer_NoXML') cc ON ll.Email = cc.Email COLLATE Latin1_General_CI_AS
INNER JOIN OPENQUERY(STOREREPLICA, 'SELECT * FROM StoreReplica.dbo.xam_Activity_OsIde_Counts WHERE VSWinCount > 0 OR XSWinCount > 0 OR XSMacCount > 0') aoic ON cc.CustomerId = aoic.CustomerId
GROUP BY Who_FirstName, Who_LastName, ll.Email
ORDER BY Activity DESC