>>How long does this query take?
>>
>>

>>SELECT Client.ClientNo,  DATEDIFF(SECOND,
>>   CASE WHEN BT.Start>Client.FirstDate THEN BT.Start ELSE Client.FirstDate END,
>>   CASE WHEN BT.[End]<Client.SecondDate THEN BT.[End] ELSE Client.SecondDate END) AS SecondValue
>>   FROM BusinessTime BT INNER JOIN Client ON
>>   
>>    BT.Start<Client.SecondDate AND BT.[End]>=Client.FirstDate
>>   WHERE BT.NoProvince=84 AND BT.NoHoliday=0

>>
>>See this Wiki Page for simplification of your ranges
>
>I am still not sure about the usage of that select. The initial example were using static values and that worked. Then, after, there was a need to evolve that approach to have those values to be bounced on the table first and last dates. If there is only one select here, with reference to the main table, how would that work?
>
>Thanks for the range simplication, I have adjusted.

The select is the same as you're currently using. I just want to see how long will it take. Your actual select should use SUM and GROUP BY Client.ClientNo, but let's first find out without SUM.

Also, are you sure that you don't have overlapping ranges? E.g. each client First/Second dates range will correspond to different rows in the BusinessTime table? That select statement (if you add to it BT dates) will tell you that.