>DECLARE @Start DATETIME >DECLARE @End DATETIME >DECLARE @Start2 DATETIME >DECLARE @End2 DATETIME > >SET @Start='2014-01-23 09:15:00' >SET @End='2014-01-29 13:16:00' >SET @Start2='2014-01-23 08:00:00' >SET @End2='2014-01-29 17:00:00' > >SELECT SUM(Temp.SecondValue)/3600 AS HourValue >FROM (SELECT 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 SecondValue > FROM BusinessTime > WHERE BusinessTime.NoProvince=84 AND > BusinessTime.Start>=@Start2 AND BusinessTime.Start<=@End2 AND > BusinessTime.[End]>=@Start2 AND BusinessTime.[End]<=@End2) Temp >>