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

This is weird UPDATE statement. I suggest to start from the SELECT with JOIN into a temp table and see how long that SELECT will work.

If the select finishes in reasonable time, then still keep the temp table approach and just do UPDATE after the select based on PK.