>The problem in here seems to be related to this nested join which makes use of a Key Lookup, which in turn uses the primary key of the table. The primary key is, by default, Clustered, and we cannot change that. On a clustered index, we cannot add included fields. However, the Key Lookup identifies an output field and I just can't benefit of it in the index.
>
>So, I tried to create another index on that primary key field, which would then be non-clustered. And, I added the included field in that one. However, the execution plan doesn't want to use that one. He sticks on using the clustered index.
>
>And, this all depends on which field we use in the condition. If I use a different field from the same table, which also has an index, I can have a totally different execution plan.

If you can post the actual query (or query closely resembling the original with structures and indexes) I may try to see what can be done to optimize it.

Also, if needed, you can use query hints and even specify which index you want it to use (INDEX hint). You may want to experiment with this and see if you can get a better plan / performance.

Also make sure to have DB compatibility level matching the version of DB.

Finally, make sure you're on the latest SP for your SQL Server version.

Also, avoid having DATEDIFF function in the right part of the query expression.