>WHERE (BusinessTime.Start>=@Start AND BusinessTime.Start<=@End) OR > (BusinessTime.[End]>=@Start AND BusinessTime.[End]<=@End)) Temp >Thanks, your approach is better. This would give this:
DECLARE @Start DATETIME DECLARE @End DATETIME SET @Start='2014-01-23 09:15:00' SET @End='2014-01-29 13:45:00' SELECT SUM(Temp.SecondValue)/3600 AS HourValue FROM (SELECT CAST(DATEDIFF(SECOND, CASE WHEN BusinessTime.Start>@Start THEN BusinessTime.Start ELSE @Start END, CASE WHEN BusinessTime.[End]<@End THEN BusinessTime.[End] ELSE @End END) AS DECIMAL(10,2)) AS SecondValue FROM BusinessTime WHERE (BusinessTime.Start>=@Start AND BusinessTime.Start<=@End) OR (BusinessTime.[End]>=@Start AND BusinessTime.[End]<=@End)) TempHowever, this gives 35.5 not 31.5.