>>;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 THEN UPDATE >>SET DayBetweenFirstDateSecondDate = T.TotalSecondsDiff/3600.0/8 >>WHEN NOT Matched THEN UPDATE >>SET DayBetweenFirstDateSecondDate = 0 >> >>>>