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 <> 'DELETED' -- these two can be inlined: and mrstatus <> 'Canceled' -- mrstatus not in ('DELETED', 'Canceled') and m.Equipment <> '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 <> 'BLDNG' ) as derived where cnt > 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.