Help with query

Question

I have following data.

I am trying to get the number of days the project stayed in a particular status. Example below of raw data, I need to be able to get the result shown.

Can I get some pointers on how I can achieve this?

Raw data

╔══════════════════════════════════╦════════╦════════════╗
║                ID                ║ STATUS ║ ENTRY_DATE ║
╠══════════════════════════════════╬════════╬════════════╣
║ 541c4cfd009784b7ad7b47a552ab7ea2 ║ WIH    ║ 9/25/2015  ║
║ 541c4cfd009784b7ad7b47a552ab7ea2 ║ CPL    ║ 9/9/2015   ║
║ 541c4cfd009784b7ad7b47a552ab7ea2 ║ CPL    ║ 7/21/2015  ║
║ 541c4cfd009784b7ad7b47a552ab7ea2 ║ CPL    ║ 7/21/2015  ║
║ 541c4cfd009784b7ad7b47a552ab7ea2 ║ CPL    ║ 7/8/2015   ║
║ 541c4cfd009784b7ad7b47a552ab7ea2 ║ CUR    ║ 6/25/2015  ║
║ 541c4cfd009784b7ad7b47a552ab7ea2 ║ CPL    ║ 6/25/2015  ║
║ 541c4cfd009784b7ad7b47a552ab7ea2 ║ CPL    ║ 6/16/2015  ║
║ 541c4cfd009784b7ad7b47a552ab7ea2 ║ TNJ    ║ 5/26/2015  ║
║ 541c4cfd009784b7ad7b47a552ab7ea2 ║ CPL    ║ 5/15/2015  ║
║ 541c4cfd009784b7ad7b47a552ab7ea2 ║ CPL    ║ 4/22/2015  ║
║ 541c4cfd009784b7ad7b47a552ab7ea2 ║ CUR    ║ 4/16/2015  ║
╚══════════════════════════════════╩════════╩════════════╝

Expected results

╔══════════════════════════════════╦════════╦════════════╦═══════════╗
║                ID                ║ STATUS ║ ENTRY_DATE ║ EXIT_DATE ║
╠══════════════════════════════════╬════════╬════════════╬═══════════╣
║ 541c4cfd009784b7ad7b47a552ab7ea2 ║ WIH    ║ 9/25/2015  ║ NULL      ║
║ 541c4cfd009784b7ad7b47a552ab7ea2 ║ CPL    ║ 7/8/2015   ║ 9/25/2015 ║
║ 541c4cfd009784b7ad7b47a552ab7ea2 ║ CUR    ║ 6/25/2015  ║ 7/8/2015  ║
║ 541c4cfd009784b7ad7b47a552ab7ea2 ║ CPL    ║ 6/16/2015  ║ 6/25/2015 ║
║ 541c4cfd009784b7ad7b47a552ab7ea2 ║ TNJ    ║ 5/26/2015  ║ 6/16/2015 ║
║ 541c4cfd009784b7ad7b47a552ab7ea2 ║ CPL    ║ 4/22/2015  ║ 5/26/2015 ║
║ 541c4cfd009784b7ad7b47a552ab7ea2 ║ CUR    ║ 4/16/2015  ║ 4/22/2015 ║
╚══════════════════════════════════╩════════╩════════════╩═══════════╝

Answer 1

You can group the data using a trick using row number - row number partitioned by status. That will create the same number for rows with the same status for a range of dates. This just takes the rows ordered by entry_date and status, but you might want to do something better for the entries on the same day:

select
  ID, status, 
  min(entry_date) as entry_date, 
  nullif (max(exit_date),'99991231') as exit_date
from
(
  select
    *,
    isnull(lead(entry_date) over (partition by id order by entry_date, status),'99991231') as exit_date,
    row_number() over (partition by id order by entry_date, status)
    - row_number() over (partition by id, status order by entry_date) as GRP
  From 
    table1
) X
group by ID, status, GRP
order by id, entry_date desc

Example in SQL Fiddle

Update: Fixed group by id, added handling for null / max exit_date for cases when the newest status has more than one row.

Answer 2

Windowed function LAG should give you the answer:

LAG (Transact-SQL)

This function allows it so you don't have to join to get [current]-nth value

SELECT 
    *,
    LAG([ENTRY_DATE], 1, NULL) OVER (
        PARTITION BY [ID], [STATE], [ENTRY_DATE] 
        ORDER BY [ENTRY_DATE]) AS EXIT_DATE
FROM table;