4

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 ║
╚══════════════════════════════════╩════════╩════════════╩═══════════╝
  • 1
    How do you determine the order of statuses happening on the same day, like the CLP & CUR on 6/25? Is there some kind of ID or other internal order? – James Z Oct 2 '15 at 15:25
  • Same day status means the status was flipped in the middle of the day. I can get the time. – cloudit Oct 2 '15 at 15:35
6

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.

1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.