>>It doesn't mean I recommend to keep your current UPDATE command as is. I'd rather re-format it to MERGE and to simplify its meaning.
>
>Yes, but, the execution plan changed from 96% allocated to those two previous select to 1% after the COALESC syntax.
>
>I am testing now the time it takes for this 30 minutes benchmark as previously taken with the version not included this syntax (the one which simply used NoProvince=84).
>
>The first two runs with the dual selects took longer than an hour so I stopped it.

Are you saying that these two queries took very long time?

;with cteProvinces AS (SELECT Client.ClientNo, 
Client.FirstDate, Client.SecondDate,
CASE WHEN Comp.NoCountry = 1 then Comp.NoProvince ELSE 84 END as NoProvince
from Client INNER JOIN NoCompany Comp ON Client.NoCompany = Comp.Numero
WHERE Client.FirstDate>'1899-12-30 00:00:00.000' AND
    Client.SecondDate>'1899-12-30 00:00:00.000' AND
    Client.SecondDate>=Client.FirstDate AND
    Client.NoCompany>0
),

cteTimes AS (SELECT SUM(DATEDIFF(SECOND,
    CASE WHEN BusinessTime.Start>Cl.FirstDate THEN BusinessTime.Start ELSE Cl.FirstDate END,
     CASE WHEN BusinessTime.[End]<Cl.SecondDate THEN BusinessTime.[End] ELSE Cl.SecondDate END)) AS [TotalSecondsDiff], Cl.ClientNo
     FROM BusinessTime INNER JOIN cteProvinces CL ON
      BusinessTime.NoProvince= Cl.NoProvince
      WHERE BusinessTime.NoHoliday=0 AND
      BusinessTime.Start<Cl.SecondDate AND
      BusinessTime.[End]>=Cl.FirstDate
   GROUP BY Cl.ClientNo
)

select * from cteTimes

?