I have a table that expresses periods of responsibility, similar to the following:

```
Key     Resp_Key      Start_Date      End_Date
1       150           2015-01-01      2015-01-25
2       150           2015-01-25      2015-02-15
3       200           2015-02-15      2015-03-01
4       150           2015-03-01      2015-04-30
5       150           2015-04-30      NULL

```

When someone is currently responsible, the column `End_Date` is `NULL`. For the above instance, the party with `Resp_Key` 150 has been responsible since March 1st (other columns may have changed on April 30th which caused another record to be created for that date, but those aren't important for this question).

I need to create a query that finds all continuous records that are connected to the current period and `Resp_Key`. In the above example, the query should return records 4 and 5, since record 5 is the current period, and record 4 is connected to it (since record 4's `End_Date` is the `Start_Date` of record 5, and they both have the same `Resp_Key`). Record 3 wouldn't be included, since `Resp_Key` is different, and records 1 and 2 wouldn't be included either, since they aren't continuous from the current record.

If the following changes/additions are made:

```
Key     Resp_Key      Start_Date      End_Date
5       150           2015-04-30      2015-05-31
6       231           2015-05-31      2015-06-30
7       150           2015-06-30      NULL

```

the query should return only record 7, since it's the current record, and the period immediately preceding it has a different `Resp_Key`.

I've been trying to compose a CTE to handle this, but I'm not sure that's the correct tool. A recursive CTE would make sense, since I can't tell beforehand how many records are part of the continuous period I'm interested in. But the CTE I composed is telling me that LEFT JOINs can't be used, and I don't see how to get around the need for it for the case where there's only one record making the latest continuous period.

```
;WITH ContinuousPeriod
AS (
    -- Current Period and key of immediately preceding period
    SELECT Current.Key, Current.Start_Date, Current.End_Date, Previous.Key AS PrevKey
    FROM PeriodTable AS Current
    LEFT JOIN PeriodTable AS Previous
        ON Previous.End_Date = Current.Start_Date
        AND Previous.Resp_Key = Current.Resp_Key
    WHERE Current.PEVNT_END_DATE IS NULL    
    UNION ALL
    . . .

```

Any suggestions?