Add JOIN_DATE to the Select list and a group by:

select JOIN_DATE , count (*) from MEMBERS,dbo.MEMBER_PROFILE

where MEMBER_PROFILE.member_no = members.member_no

AND JOIN_DATE between '07-01-2013 00:01' and '07-31-2013 11:59'

and email <> 'selfbuy_customer@cafepress.com'

and ROOT_FOLDER_NO is not null

and email not like '%bvt.bvt'

GROUP BY JOIN_DATE