title: How to get the latest dates with CTE
body: <p>I have this Common Table Expression which should pull data for clients who have received services 'S' after '2015-07-01' and latest enrollment 'e' date should be before '2015-07-01’ but the result set includes clients who have been re-enrolled after '2015-07-01’ because they also have multiple enrollment like services. Not sure what clause to include to make sure their LATEST enrollment begin date is before '2015-07-01’.</p>
  
  <pre><code>WITH PhoneLog AS
  (
  select s.ProvidedToEntityID, 
  concat (c.FirstName,' ', C.LastName) as ClientName,
  ecp.CellPhone, 
   ecp.HomePhone, 
   case ecp.PhoneVoiceOptIn
  when 1 then 'Yes'
  when 2 then 'No'
  Else 'Unknown'
  End as ContactViaPhone,
    S.BeginDate ServiceStart, 
   e.BeginDate  EnrollmentBeginDate,
  case 
  when Dateadd(YY,Datediff(YY,c.birthdate,getdate()), c.birthdate)&gt; getdate()
  then datediff(YY,birthdate,getdate())
  else datediff(YY,birthdate,getdate())
  end as 'Current Age',
  e.X_ProgramStatus as 'program Status',
  ROW_NUMBER () over (Partition by s.providedtoEntityID order By s.begindate Desc ) as ClientCount 
    from 
  Client c
  Join Service s on c.EntityID = s.ProvidedByEntityID
  join servicetype st on s.ServiceTypeID = st.ServiceTypeID
  join Enrollmentmember Em on c.EntityID = em.ClientID
  join enrollment e on em.EnrollmentID = e.EnrollmentID
  join Entitycontactpreference ecp on c.entityid = ecp.entityid
  where s.BeginDate &gt;= '2015-07-01'
  and e.BeginDate &lt; '2015-07-01'
  --and s.BeginDate&gt;e.BeginDate
  and ecp.PhoneVoiceOptIn = 1
  )
  SELECT
  ProvidedToEntityID,
  ClientName,
  [Current Age],
  cellphone,
  Homephone,
  ContactViaPhone,
  ServiceStart,
  EnrollmentBeginDate,
  ClientCount,
  [program Status]
  
  FROM
      PhoneLog
  WHERE
      ClientCount = 1
  </code></pre>