Adding a criteria to a sub-query

I have the following subquery.  I am not able to add an additional criteria.
The subquery is:

, (select TOP 1 member_coverage_history.enrollment_date
      from CCMSPROD.ccmsdba.member_coverage_history member_coverage_history (nolock)
      where member.member_id = member_coverage_history.member_id
      AND member_coverage_history.benefit_plan in ('02')
        order by enrollment_date desc) AS enrollment_date  


I wanted to add the criteria of member_coverage_history.enrollment_date <> {ts '2011-05-01 00:00:00'}

This is to eliminate any records that have a date of 5-1-2011.  Is this possible in a subquery with a top1?
moriniaAdvanced Analytics AnalystAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
If your enrollment date has no time portion...


, (select TOP 1 member_coverage_history.enrollment_date
      from CCMSPROD.ccmsdba.member_coverage_history member_coverage_history (nolock)
      where member.member_id = member_coverage_history.member_id
      AND member_coverage_history.benefit_plan in ('02')
      AND member_coverage_history.enrollment_date <> '2011-05-01'
        order by enrollment_date desc) AS enrollment_date


If there is a time portion:


, (select TOP 1 member_coverage_history.enrollment_date
      from CCMSPROD.ccmsdba.member_coverage_history member_coverage_history (nolock)
      where member.member_id = member_coverage_history.member_id
      AND member_coverage_history.benefit_plan in ('02')
      AND (member_coverage_history.enrollment_date < '2011-05-01' OR
               member_coverage_history.enrollment_date >= '2011-05-02')
        order by enrollment_date desc) AS enrollment_date
0
 
jasonduanConnect With a Mentor Commented:
yes. you can.

, (select TOP 1 member_coverage_history.enrollment_date
      from CCMSPROD.ccmsdba.member_coverage_history member_coverage_history (nolock)
      where member.member_id = member_coverage_history.member_id
      AND member_coverage_history.benefit_plan in ('02')
      AND member_coverage_history.enrollment_date <> '2011-05-01'     order by enrollment_date desc) AS enrollment_date  

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.