• There's several different avenues of approach here and I'm sure I won't see them all without having the DDL to play around with.

    The first thing I notice is that a salesman may not have sales returned from both tables, which is why you're outer joining the tables together. If this happens, you'll get a NULL sum, so I'd change the query to include ISNULLs as follows:

    SELECT salesman.Name, ISNULL(COUNT(SalesOld.SalesManID), 0) + ISNULL(COUNT(SalesNew.SalesManID), 0) NumberOfSales

    FROM salesman

    LEFT OUTER JOIN SalesOld ON SalesOld.SalesManID = salesman.SalesManID AND SalesOld.SalesDate >= '01.01.2012' AND SalesOld.SalesDate < '01.01.2013'

    LEFT OUTER JOIN SalesNew ON SalesNew.SalesManID = salesman.SalesManID AND SalesNew.SalesDate >= '01.01.2013' AND SalesNew.SalesDate < '01.01.2014'

    GROUP BY salesman.Name

    HAVING ISNULL(COUNT(SalesOld.SalesManID), 0) + ISNULL(Count(SalesNew.SalesManID), 0) >1000;

    The next thing I'd do is to make sure things are indexed. From names only, I presume salesman.SalesManID is the primary key and SalesOld.SalesManID and SalesNew.SalesManID are the foreign keys by design, so make sure they are defined as such. I'd then make sure nonclustered indexes are defined on SalesOld and SalesNew.

    CREATE NONCLUSTERED INDEX SalesOld_IDX01 ON SalesOld(SalesManID, SalesDate);

    CREATE NONCLUSTERED INDEX SalesNew_IDX01 ON SalesOld(SalesManID, SalesDate);

    If you have other columns to compare or return from the salesman table, you'll want to look at creating a covering index there as well.

    HTH