>

>;with cteProvinces AS (SELECT Client.*, 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
>      BusinessTime.NoHoliday=0 AND
>      BusinessTime.Start<Cl.SecondDate AND
>      BusinessTime.[End]>=Cl.FirstDate
>   GROUP BY Cl.ClientNo
>)
>
>MERGE Client 
>USING cteTimes T ON Client.ClientNo = T.ClientNO
>WHEN Matched
>SET DayBetweenFirstDateSecondDate = T.TotalSecondsDiff/3600.0/8
>WHEN NOT Matched 
>SET DayBetweenFirstDateSecondDate  = 0
>
>

>
>Please verify MERGE syntax and also first try it as a select instead of MERGE to check the result. This is from the top of my head.

So, this is an approach to consider on a large scale Company table. In my case, it is only 2000 records. Even on a 4 million record table, so far, based on my tests, this does not seem to be pretty much of an impact as SQL Server caches most of that, if not the entire table. I thought there could have been something applicable in the base syntax. But, this refactors the entire command and that would be really too much.