>This morning, we ended up with a successful approach to calculate the number of working hours between two dates, based on a table which contains working shifts.
>
>When executing the command, it went ok.
>
>Now, I included that select into an update so I can update all the records of the table.
>
>This went as follow:
>
>

>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)
>

>
>If I would have executed this in code, it would have taken approximately 10 to 20 minutes.
>
>So, the goal to build it all in one command was to target at optimization.
>
>However, this type of select does not seem to fit well with the update. The command never finishes. This has been running for two hours. I am trying to determine what is causing this.
>
>I had expected this one to complete in 3 to 5 minutes.

How long does this query take?

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=0

See this Wiki Page for simplification of your ranges