Recursive Join to get Spans
This is a Gap &amp; Island problem. Plenty of sites talk about it if you Google it. Here is just the first link I clicked among many others: Solving Gaps and Islands with Enhanced Window Functions

Here is how the query works:


YYYYMM is a varchar and does not give consecutive numbers. Therefore it first changes them to proper date format.
then ROW_NUMBER() will number them by group of Member and [Plan]
consecutive months will have consecutive rn and once rn is removed from the date, they will have identical values for DATEADD(month, rn, l1.dt)

Member  Plan    dt          rn  =&gt; DATEADD(month, rn, l1.dt)
2       123     2015-12-01  1   =&gt; 2016-01-01
2       123     2015-11-01  2   =&gt; 2016-01-01
2       123     2015-10-01  3   =&gt; 2016-01-01
2       123     2015-06-01  4   =&gt; 2015-10-01
2       123     2015-05-01  5   =&gt; 2015-10-01

DATEADD(month, rn, l1.dt) is finally used to group them along with Member and Plan


Query Oracle:

WITH dates AS(
    SELECT DISTINCT Member, "Plan"
        , dt = CAST(YYYYMM || '01' as date)
    FROM data
), list AS (
    SELECT *
        , rn = ROW_NUMBER() OVER(PARTITION BY Member, "Plan" ORDER BY dt DESC)
    FROM dates d
)
SELECT l1.Member
    , MIN(l1.dt) as YYYYMM_Begin
    , MAX(l1.dt) as YYYYMM_End
    , l1."Plan"
FROM list l1
GROUP BY Member, "Plan", ADD_MONTHS(l1.dt, rn);


Oracle [SQL Fiddle][] 

Query SQL Server:

WITH dates AS(
    SELECT DISTINCT Member, [Plan]
        , dt = CAST(YYYYMM+'01' as date)
    FROM @data
), list AS (
    SELECT *
        , rn = ROW_NUMBER() OVER(PARTITION BY Member, [Plan] ORDER BY dt DESC)
    FROM dates d
)
SELECT l1.Member
    , MIN(l1.dt) as YYYYMM_Begin
    , MAX(l1.dt) as YYYYMM_End
    , l1.[Plan]
FROM list l1
GROUP BY Member, [Plan], DATEADD(month, rn, l1.dt);


SQL Server SQL Fiddle

Output:

Member  YYYYMM_Begin    YYYYMM_End  Plan
1       2016-01-01      2016-02-01  123
1       2015-10-01      2016-01-01  456
2       2015-05-01      2015-06-01  123
2       2015-10-01      2015-12-01  123