I suggest to re-write this statement.

1. Start from a select statement to return what you will need.

2. Once you get your select statement working properly, turn it into MERGE command.




>My initial select is like this:
>
>

>UPDATE Client
> SET DayBetweenFirstAcceptSoldDate=
>  CASE
>   WHEN Client.FirstAcceptDate>'1899-12-30 00:00:00.000' AND
>    Client.QueryDate>'1899-12-30 00:00:00.000' AND
>    Client.QueryDate>=Client.FirstAcceptDate AND
>    (Client.NoCompany>0 OR Client.NoResearch>0) THEN
>    (SELECT COALESCE(SUM(Temp.[Second])/3600.0/8,0)
>    FROM (SELECT DATEDIFF(SECOND,
>     CASE WHEN BusinessTime.Start>Client.FirstAcceptDate THEN BusinessTime.Start ELSE Client.FirstAcceptDate END,
>     CASE WHEN BusinessTime.[End]<Client.QueryDate THEN BusinessTime.[End] ELSE Client.QueryDate END) AS [Second]
>     FROM BusinessTime
>     WHERE BusinessTime.NoProvince=
>       (SELECT COALESCE((SELECT Company.NoProvince FROM Company WHERE Company.NoCountry=1 AND Company.Numero=Client.NoCompany),84))
>      BusinessTime.NoHoliday=0 AND
>      BusinessTime.Start<Client.QueryDate AND
>      BusinessTime.[End]>=Client.FirstAcceptDate) Temp)
>   ELSE 0
>  END
>

>
>Now, I need to condition the WHERE clause to support one way or another:
>
>

>UPDATE Client
> SET DayBetweenFirstAcceptSoldDate=
>  CASE
>   WHEN Client.FirstAcceptDate>'1899-12-30 00:00:00.000' AND
>    Client.QueryDate>'1899-12-30 00:00:00.000' AND
>    Client.QueryDate>=Client.FirstAcceptDate AND
>    (Client.NoCompany>0 OR Client.NoResearch>0) THEN
>    (SELECT COALESCE(SUM(Temp.[Second])/3600.0/8,0)
>    FROM (SELECT DATEDIFF(SECOND,
>     CASE WHEN BusinessTime.Start>Client.FirstAcceptDate THEN BusinessTime.Start ELSE Client.FirstAcceptDate END,
>     CASE WHEN BusinessTime.[End]<Client.QueryDate THEN BusinessTime.[End] ELSE Client.QueryDate END) AS [Second]
>     FROM BusinessTime
>     WHERE BusinessTime.NoProvince=
>      CASE
>       WHEN Client.NoCompany>0 THEN
>        (SELECT COALESCE((SELECT Company.NoProvince FROM Company WHERE Company.NoCountry=1 AND Company.Numero=Client.NoCompany),84))
>       ELSE
>        (SELECT COALESCE((SELECT Research.NoProvince FROM Research WHERE Research.NoCountry=1 AND Research.Numero=Client.NoResearch),84))
>       END AND 
>      BusinessTime.NoHoliday=0 AND
>      BusinessTime.Start<Client.QueryDate AND
>      BusinessTime.[End]>=Client.FirstAcceptDate) Temp)
>   ELSE 0
>  END
>

>
>However, this is having some weird results on SQL Server. I can execute that command, which used to run for about 3 minutes, and now it can takes up to 24 hours and still not finished.
>
>The Client table has 3 million records and all related indexes are there.
>
>I assume the syntax I am using is not a proper approach.
>
>Anyone would know what it could be?