>UPDATE Client > SET DayBetweenFirstDateSecondDate= > (SELECT SUM(Temp.SecondValue)/3600.0/8 AS HourValue > FROM (SELECT DATEDIFF(SECOND, > CASE WHEN BusinessTime.Start>Client.FirstDate THEN BusinessTime.Start ELSE Client.FirstDate END, > CASE WHEN BusinessTime.[End]<Client.SecondDate THEN BusinessTime.[End] ELSE Client.SecondDate END) AS SecondValue > FROM BusinessTime > WHERE BusinessTime.NoProvince=84 AND BusinessTime.NoHoliday=0 AND > ((BusinessTime.Start>=Client.FirstDate AND BusinessTime.Start<=Client.SecondDate) OR > (BusinessTime.[End]>=Client.FirstDate AND BusinessTime.[End]<=Client.SecondDate))) Temp) >>
SELECT Client.ClientNo, DATEDIFF(SECOND, CASE WHEN BT.Start>Client.FirstDate THEN BT.Start ELSE Client.FirstDate END, CASE WHEN BT.[End]<Client.SecondDate THEN BT.[End] ELSE Client.SecondDate END) AS SecondValue FROM BusinessTime BT INNER JOIN Client ON BT.Start<Client.SecondDate AND BT.[End]>=Client.FirstDate WHERE BT.NoProvince=84 AND BT.NoHoliday=0See this Wiki Page for simplification of your ranges