select STATE,COUNT(state)
from (select T.*,z.COUNTY,ROW_NUMBER() over (partition by t.phone order by t.date_appen desc) as recid
from TestDB.tru.truecredit t
left join zipinfo.dbo.GeoLookup z on z.zip = t.zip
left join Residential.dbo.dnc d on d.tl1 = t.PHONE
left join Korey.dbo.supp s
	on LEFT(s.lname,5) = LEFT(t.lname,5)
	and LEFT(s.address,10) = LEFT(t.address,10)
	and LEFT(s.zip,5) = LEFT(t.zip,5)
where 1=1
		and t.date_appen between getdate()-180 and getdate()-30
		and d.tl1 is null
		and t.phone>''
		and s.lname is null
		and s.address is null
		and s.zip is null
		and t.STATE in ('AL','AK','AZ','CA','CT','DC','FL','IL','IN','LA','MD',
						'MA','MI','MT','NE','NH','NM','NY','ND','OK','OR','PA',
						'SD','TX')
		and t.rev16>=20000
	) as f
where recid=1
group by STATE order by state