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.