There are other columns in this table I have left out (name, ID, etc.)
So in the above, you'll notice there are 6 rows which have a search_date of 20150622. I will not need any of those rows, because the number of rows with that date are >5. You'll notice there are only 4 rows which have a search_date of 20150701. That's an example of the rows I need returned from my query.
select search_date, award_year_token
from yourtable t1
join
(
select search_date
from yourtable t2
group by search_date
having count(*) < 5
) q
on t1.search_date = q.search_date
select t1.search_date, award_year_token
from manual.dbo.ClearingHouse_Student_History t1
join
(
select search_date
from manual.dbo.ClearingHouse_Student_History t2
group by search_date
having count(*) < 5
) q
on t1.search_date = q.search_date
And, yikes - they're in as varchar(8)! (I didn't set these up )
ok, then there's probably something different about the way 20150701 is stored. Watch out for leading spaces, imbedded tabs, etc. These will cause the join to fail.
As a test, run the subquery on its own to see if 20150701 appears.
When I right-click I go Tasks > Generate Scripts > Choose my table > and then choose "script to clipboard" and the only option is Finish -- there's no "advanced" and this is what it gives me....
USE [Manual]
GO
/****** Object: Table [dbo].[ClearingHouse_Student_History] Script Date: 08/06/2015 11:36:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ClearingHouse_Student_History](
[student_ssn] [varchar](9) NULL,
[first_name] [varchar](15) NULL,
[middle_init] [char](1) NULL,
[last_name] [varchar](16) NULL,
[dob] [varchar](8) NULL,
[search_date] [varchar](8) NULL,
[award_year_token] [varchar](4) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
In the Script Options (one of the first dialog windows in the wizard) there is a "Script data" option which is FALSE by default. Including that, and scripting just the one table, should give you a Create Table script together with something to insert the data into it - which you can then hand-edit to slim down to the actual data you want and/or to obfuscate anything confidential.
I ended up figuring out the problem. Your query was running just as it should. But then I realized that award_year_token 2014 and 2015 could both have the same search_date. So I had to narrow it down to search only for those with less than 5 of the same search_date with award_year_token=2014.
select t1.search_date, award_year_token
from manual.dbo.ClearingHouse_Student_History t1
join
(
select search_date
from manual.dbo.ClearingHouse_Student_History t2
where award_year_token=2014
group by search_date
having count(*) < 5
) q
on t1.search_date = q.search_date
where award_year_token=2014