Quote:Why SQL server query running too slow around 2+ hours?
All in your title, because : "It has lots of joins, left joins and ctes."
You start to understand why SQL database design and optimization is a whole job.
We have no way to turn you in an expert over night.
A good database design is a match between tables structures, tables indexes and queries. The way one is designed impacts the 2 others.
Unfortunately, there is no magic trick to solve your problem, only a deep study of your database can pinpoint where is the mismatch.
Since you didn't have any detail, it is impossible to help you any further than general indications.