Calculate total running time from start/stop timestamps

I have a table containing a number of timestamps per day, they represents start and stop events.

ID TimeStamp
----------------------
1  2008-01-01 07:00:00
1  2008-01-01 08:15:00
1  2008-01-01 10:00:00
1  2008-01-01 11:00:00
1  2008-01-02 10:30:00
1  2008-01-02 12:00:00

I would开发者_运维问答 like to calcuate the total running time per day, like this:

ID Date       RunningTime
-------------------------
1  2008-01-01 02:15:00
1  2008-01-02 01:30:00

Do anyone have a nice T-SQL solution for my problem?

---

WITH    q AS
        (
        SELECT  *,
                CONVERT(DATETIME, CONVERT(VARCHAR(8), TimeStamp, 112), 112) AS dte,
                ROW_NUMBER() OVER (PARTITION BY id, CONVERT(DATETIME, CONVERT(VARCHAR(8), TimeStamp, 112), 112) ORDER BY TimeStamp) AS rn
        FROM    mytable
        )
SELECT  qb.id, qb.dte, SUM(DATEDIFF(second, qb.TimeStamp, qe.TimeStamp))
FROM    q qb
JOIN    q qe
ON      qe.id = qb.id
        AND qe.dte = qb.dte
        AND qe.rn = qb.rn + 1
WHERE   qb.rn % 2 = 1
GROUP BY
        qb.id, qb.dte

This assumes that every record open on a certain day should also be closed on the same day.