SQL - pretty complicated one

I need to make a query on 2 tabl开发者_如何学编程es. First one has the following fields:

• id [pk ,int]
• dt [datetime]

Second table:

• sid [pk, int]
• sdt [datetime]

Now, the first table holds the start time of some action and the second table holds sub-actions that happen after the first action.

I need to get the average time from the start of the action to the end (that means to the last sid before the next id starts). To be clear - I need the average time from each 'id' field to last 'sid' field before the next 'id' happened (of course using the 'dt' and 'sdt' fields).

---

In SQL Server:

WITH    main AS
        (
        SELECT  *,
                ROW_NUMBER() OVER (ORDER BY dt) rn
        FROM    t1
        )
SELECT  AVG(DATEDIFF(d, tc.dt, i.sdt))
FROM    main tc
LEFT JOIN
        main tn
ON      tn.rn = tc.rn + 1
OUTER APPLY
        (
        SELECT  TOP 1 sdt
        FROM    t2 i
        WHERE   sdt >= tc.dt
                AND sdt < COALESCE(tn.dt, (SELECT MAX(sdt FROM t2))
        ORDER BY
                i.sdt DESC
        ) sdt

This one may be more efficient if you have lots of records in t1 but few in t2:

SELECT  AVG(DATEDIFF(s, dt, msdt))
FROM    (
        SELECT  MAX(sdt) AS msdt, id, dt
        FROM    t2 i
        CROSS APPLY
                (
                SELECT  TOP 1 *
                FROM    t1
                WHERE   dt <= sdt
                ORDER BY
                        dt DESC
                ) t
        GROUP BY
                id
        ) q

---

My SQL is a little rusty, but I'm thinking the following is the right idea (at least logically, even if I have it slightly off syntactically).

select a.id as id, avg(a.time) as avg_time
from 
(select s.sid as id, (s.sdt - f.dt) as time
from firstable as f, secondtable as s
where f.id = s.sid) as a
group by a.id