select i.firstname, i.lastname, count(*)
from (
select m.memberid, m.registrationDate,
	(select min(dateAdd )
	   from memberPayment as mp
	  where mp.memberId = m.memberId
		and mp.payForId = 1) as payDate,
	(select count(*)
	   from memberPayment as mp2
	  where mp2.memberId = m.memberId
		and mp2.payForId = 1) as newPayments,
	sems.firstGrad, sems.instructorId
  from members as m
  left join (
	select st.memberId, st.instructorId, st.dateGrad as firstGrad, m2.registrationDate
	  from seminarsTaken as st
	  join members as m2
	    on st.memberId = m2.memberId
	 where st.dateGrad < m2.registrationDate
	 group by st.memberId, st.instructorId, st.dateGrad, m2.registrationDate
	having st.dateGrad =
		(select max(dateGrad)
		   from seminarsTaken as st2
		  where st2.memberId = st.memberId
			and st2.dateGrad < m2.registrationDate)
	) as sems
    on m.memberId = sems.memberId
 where m.membership in (3,4,5,6,7)
   -- and m.renewByDate > getDate()
   and m.registrationDate between '7/1/2007' and '6/30/2008'
) as mC
  join members as i
    on i.memberId = mC.instructorId
 group by i.lastname, i.firstname
 order by i.lastname, i.firstname