You have a lot of performance hurdles to overcome in here. You have lots of nonSARGable code. You have functions all over the place in your join and where predicates. Each of these functions renders any indexes useless. Then you are obviously storing datetime information in varchar columns. Again, indexes are useless here because the first thing that happens is every single row must first be converted into the datatype it should have been in already.
And take heed with the warnings about littering your database with NOLOCK. The article Luis referenced explain what the hint is really doing.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/