Select Members that are enrolled in October 2013 Only Excluding other months

Good Evening Experts,

I have a table named member_elig_history with a Mem_No Varchar2 and Status_Dt Varchar 2 ( i know the status_dt is not a date field however this table contains the month a member was enrolled so for example if a member was enrolled between 1/1/2013 and 4/1/2013 there would be entries like the following '201301','201302' and '201303' I'm trying to find members that are 'new' for october 2013, in other words these members have not existed in this table for previous months during 2013 just for value '201310'.

I've attached a visual representation to assist.

THank you much.
Visio-Visual-Representation2.pdf
Dare626Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
why not just ask for the Status_Dt value you need?

SELECT
a.mem_no,
a.status_dt
FROM careware.mem_elig_hist a
WHERE a.status_dt = '201310' -- << just for this month, change to suit

or, I'm not understanding the problem being faced.
0
Dare626Author Commented:
Your correct, but i want to exclude if that member shows up in previous month, so say for example that member shows up for this month '201310' but theres also an entry for August '201308' then i dont want to identify that member. Only if there's an entry for this month and no other previous months.
0
PortletPaulfreelancerCommented:
ok, got it - back soon
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

PortletPaulfreelancerCommented:
try Query 1 below (Query 2 is for comparison):
**Query 1**:

    SELECT
           a.mem_no
         , MIN(a.status_dt) AS status_dt
         , count(*)
    FROM mem_elig_hist a
    GROUP BY
    a.mem_no
    HAVING count(*) = 1 AND min(a.status_dt) = '201310'
    ORDER BY
           mem_no
         , status_dt
    

**[Results][2]**:
    
    |     MEM_NO | STATUS_DT | COUNT(*) |
    |------------|-----------|----------|
    | 1000555*01 |    201310 |        1 |
    | 1111111*01 |    201310 |        1 |
    |  232323*01 |    201310 |        1 |
    | 2459875*01 |    201310 |        1 |
    | 6666665*01 |    201310 |        1 |
    | 6666666*01 |    201310 |        1 |


**Query 2**:

    SELECT
           a.mem_no
         , a.status_dt
    FROM mem_elig_hist a
    WHERE a.status_dt = '201310'
    ORDER BY
           a.mem_no
         , a.status_dt
    

**[Results][3]**:
    
    |     MEM_NO | STATUS_DT |
    |------------|-----------|
    | 1000000*01 |    201310 |
    | 1000555*01 |    201310 |
    | 1111111*01 |    201310 |
    |  232323*01 |    201310 |
    | 2459875*01 |    201310 |
    | 6666665*01 |    201310 |
    | 6666666*01 |    201310 |
    | 7777777*01 |    201310 |
    | 9999999*01 |    201310 |



  [1]: http://sqlfiddle.com/#!4/4d30b/18

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dare626Author Commented:
Thank you very much i believe you solved it, let me do some quick runs with it.
0
Dare626Author Commented:
Thank you very much
0
PortletPaulfreelancerCommented:
No trouble - thank you for the question. Keep in mind that the having clause is damn handy :)

Cheers, Paul
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.