问时间戳之间的求和

EN

declare @datetimes table (id int primary key identity(1,1), startdatetime datetime, enddatetime datetime)
insert into @datetimes (startdatetime,enddatetime) values
      ('2014-08-26 08:00:00','2014-08-27 09:00:00')
    , ('2014-07-16 08:00:00','2014-07-28 09:00:00')

declare @defaultDayStart time = '08:00:00', @defaultDayEnd time = '17:00:00'

select T.ID, sum(Z.ShadowHours)
from (
        select id, startdatetime, enddatetime, datediff(hh,startdatetime,enddatetime) TotalTime
        from @datetimes 
    ) T
    CROSS APPLY (
            SELECT
                RowID-1 AS ShadowDays
                , CASE
                    WHEN DATENAME(dw,DATEADD(dd, RowID - 1,t.startdatetime)) IN ('Saturday', 'Sunday') THEN 0
                    ELSE DATEDIFF(hh, CASE RowID WHEN MIN(RowID)OVER(PARTITION BY t.id) THEN t.startdatetime ELSE CONVERT(DATETIME,CONVERT(DATE, DATEADD(dd, RowID - 1,t.startdatetime))) + @defaultDayStart END
                            , CASE RowID WHEN MAX(RowID)OVER(PARTITION BY t.id) THEN t.enddatetime ELSE CONVERT(DATETIME,CONVERT(DATE, DATEADD(dd, RowID - 1,t.startdatetime))) + @defaultDayEnd END
                            )
                END AS ShadowHours
                , DATENAME(dw,DATEADD(dd, RowID - 1,t.startdatetime)) wd
            FROM (
                SELECT ROW_NUMBER()OVER(ORDER BY S.NAME) AS RowID
                FROM master..spt_values S
                ) X
            WHERE DATEDIFF(dd,startdatetime,enddatetime) + 1 >= RowID
        ) Z
GROUP BY T.id