SQL Question: Getting Datediff in days elapsed for each record in a group.

Question

Given this table:

How can I get the datediff in days between each status_date for each group of ID_Number? In other words I need to find the number of elapsed days for each status that the ID_Number has been given.

Some things to know:

• All ID_Number will have a received_date which should be the earliest date for each ID_Number (but app doesn't enforce)
• For each ID_Number there will be a status with a corresponding status_date which is the date that the ID_Number was given that particular status.
• The status column doesn't always necessarily go in the same order every time (app doesn't enforce)
• All ID_Number will have a closed_date which should be the latest date (but app doesn't enforce)

Sample output: So for ID_Number 2001, the first date (received_date) is 2009-05-02 and the next date you encounter has a status of 'open' and is 2009-05-02 so elapsed days is 0. Moving on to the next date encountered is 2009-05-10 with a status of 'invest' and the elapsed days is 8 counting from the prior date. The next date encountered is 2009-07-11 and the elapsed days is 62 counting from the previous date.

Edited to add:

Is it possible to have the elapsed days end up as a column on this table/view? I also forgot to add that this is SQL Server 2000.

Answer 1

Some sample output would really help, but this is a guess at what you mean, assuming you want that information for each ID_Number/Status combination:

select ID_Number, Status, EndDate - StartDate as DaysElapsed
from (
    select ID_Number, Status, min(coalesce(received_date, status_date)) as StartDate, max(coalesce(closed_date, status_date)) as EndDate
    from Table1
    group by ID_Number, Status
) a

Answer 2

The tricky bit is determining the previous status and putting it on the same row as the current status. It would be simplified a little if there were a correlation between Key and StatusDate (i.e. that Key(x) > Key(y) always implies StatusDate(x) >= StatusDate(y)). Unfortunately, that doesn't seem to be the case.

PS: I am assuming Key is a unique identifier on your table; you haven't said anything to indicate otherwise.

SELECT  Key,
        ID_Number, 
        (
        SELECT  TOP 1 Key
        FROM    StatusUpdates prev
        WHERE   (prev.ID_Number = cur.ID_Number)
            AND (   (prev.StatusDate < cur.StatusDate)
                OR  (   prev.StatusDate = cur.StatusDate
                    AND prev.Key < cur.Key
                    )
                )
        ORDER BY StatusDate, Key /*Consider index on (ID_Number, StatusDate, Key)*/
        ) PrevKey
FROM    StatusUpdates cur

Once you have this as a basis, it's easy to extrapolate to any other info you need from the current or previous StatusUpdate. E.g.

SELECT  c.*,
        p.Status AS PrevStatus,
        p.StatusDate AS PrevStatusDate,
        DATEDIFF(d, c.StatusDate, p.StatusDate) AS DaysElapsed
FROM    (
        SELECT  Key,
                ID_Number, 
                Status,
                SattusDate,
                (
                SELECT  TOP 1 Key
                FROM    StatusUpdates prev
                WHERE   (prev.ID_Number = cur.ID_Number)
                    AND (   (prev.StatusDate < cur.StatusDate)
                        OR  (   prev.StatusDate = cur.StatusDate
                            AND prev.Key < cur.Key
                            )
                        )
                ORDER BY StatusDate, Key
                ) PrevKey
        FROM    StatusUpdates cur
        ) c
        JOIN StatusUpdates p ON
            p.Key = c.PrevKey