从开始/停止时间戳计算总运行时间

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

这假设在某一天打开的每个记录也应该在同一天关闭。