• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 289
  • Last Modified:

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?
0
morinia
Asked:
morinia
2 Solutions
 
jasonduanCommented:
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
 
Patrick MatthewsCommented:
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now