Datediff help needed

[Karsten]

Hi all,

I'm searching for a solution on the following.

In a table I have the following columns:

Row Id - Call Id - Date ---------- Action
001 ------ 230 ---- 10-10-2007 - Logged
002 ------ 230 ---- 10-12-2007 - Closed
003 ------ 230 ---- 10-19-2007 - Reactivated
004 ------ 230 ---- 10-21-2007 - Closed
005 ------ 231 ---- 10-22-2007 - Logged
etc...

row id = unique
Action can be:
- Logged
- Closed
- Reactivated

In this table more actions can be logged on one Call id.

I need a query what calculates the difference in days between logged and (the first following) closed added with the days from (also the first coming) Reactivated untill (the next) closed as long as there are records for that call id.
ordering on row_id will ensure the right order.

I've worked myself to the bone to figure this one out on mssql but can't get there.

I've tried to make a cursor with log and close time in one record, then union it with the reactivated and closed records but I failed to get it work.

Is there anyone who can give me a hand please?

Thanks in advance.

Karsten

[camel]

Interestingly a post just the other day on this site "Complex query (compare records in same table / join a 2nd table) " was very similar in nature, and unlike your happy situation did not start off with a unique RowID in hand. Have a look at my solution in that post as it should show how to you deal with your query without any need for a cursor.

[Karsten]

Oh yes, I read the post (and many others before posting in here)
forgot to mention, I'm not able to create a table in the database.
I've only read (select) access... :-(

is there any way around?

[camel]

I think this what you want, tested it with your small sample of records.

The inner query (AS C) finds records that are "Closed" and the query around it (AS D) finds records "Closed" that were preceded by "Reactivated". This is the same gig as the post I referrred you to. Finally the outer query joins on CallID to compare the LoggedDate to the ClosedDate, knowing from inner query (AS D) that only Closed preceded by Reactivated records are involved.

 
SELECT    R.CallID, 
    DATEDIFF(d, R.[Date], D.ClosedDate) ClosedMinusLoggedInDays  
FROM    Records R
JOIN        (SELECT    R.CallID, C.ClosedDate
    FROM    Records R
    JOIN   (SELECT    RowID,CallID, [Date] ClosedDate
               FROM    Records R
               WHERE    Action = 'Closed') AS C
          ON R.CallID = C.CallID
                    AND (R.RowID + 1) = C.RowID
    WHERE    R.Action = 'Reactivated') AS D
          ON    R.CallID = D.CallID
WHERE    R.Action = 'Logged'    
ORDER BY R.RowID