How to select * if sets of data in two columns matches >= 4 times
You are counting the occurrences of Unit and Equipment across the entire dataset, not specifically within the period, in which some combinations may well occur more than 3 times while others fewer times or never.

Therefore, you need to apply the same date range filter to the derived table to make the results reflect your requirements:

select   m.mrid, a.unit, a.Region, m.Equipment
from     MASTER36 m
join     MASTER36_ABDATA a
on       m.mrid = a.mrid
join     (select   a.unit, m.Equipment
           from     MASTER36 m
           join     MASTER36_ABDATA a
           on       m.mrid = a.mrid
           where    mrSUBMITDATE between '2/7/17' and '3/7/17'
           group by a.unit, m.Equipment 
           having   count(*) > 3
         ) t2
on       a.unit = t2.unit
and      m.Equipment = t2.Equipment 
where    mrSUBMITDATE between '2/7/17' and '3/7/17'
and      mrstatus &lt;> 'DELETED'   -- these two can be inlined:
and      mrstatus &lt;> 'Canceled'  -- mrstatus not in ('DELETED', 'Canceled')
and      m.Equipment  &lt;> 'BLDNG';


Note: as you can see, I have also rewritten the join to the derived table to use the explicit join syntax, for consistency.

You could also try this alternative which references each table only once and does not repeat any part of the logic:

select   mrid, unit, Region, Equipment
from     (
           select   m.mrid, a.unit, a.Region, m.Equipment, mrStatus,
                    cnt = count(*) over (partition by a.unit, m.Equipment)
           from     MASTER36 m
           join     MASTER36_ABDATA a
           where    mrSUBMITDATE between '2/7/17' and '3/7/17'
           and      m.Equipment  &lt;&gt; 'BLDNG'
         ) as derived
where    cnt &gt; 3
and      mrstatus not in ('DELETED', 'Canceled');


Instead of counting rows with a normal aggregate function, the query uses a window aggregate function. A window aggregate function lets you return aggregate results along with non-aggregate data.

In the above query you can see that part of the logic is applied directly to the joined tables, at the scope where the rows are counted. Other filters are applied at the outer level, to the derived table, so as not to affect the results of count(*), thus matching the logic of the original query.

The filter on count(*) is also applied outside the derived table, because computed columns cannot be filtered on in the same scope where they are defined and window functions in particular cannot be used in a WHERE clause.

As a final note, please consider these points for maintainability and clarity of your queries:


Use a locale-/culture-independent format for your date/time literals. As you are using dates, consider YYYYMMDD.
Use open-ended ranges for dates and/or times.
Always qualify columns with table names or aliases where more than one table is involved.
End your statements with the ; statement terminator.