Dear Thom A and Sergiy,

Many thanks for your advice. After added "Order by" to those "Top 1" subqueries, its speed has improved dramatically. The original query speed was 2 mins...

Thanks,Steve for the head up. I will try your suggestions and find out the issue.

Hi Steve,
I feel your frustration on my previous question, I apologize for that. English is my second language and perhaps I didn't describe the question clearly in my...

Thank you, ChrisM and Muthu for the advices.
It gonna take me sometimes to digest and restructure the query base on your points.
Thanks again to all of you.

Hi Lynn, Grant and ChrisM,
Combine all of your above suggestions, I modified the queries into the following and the results are, 
1. First execution time in local network production...

Hi Lynn, Grant and ChrisM,
Very much appreciated for your input. I will try all of your suggestions given and update you about the outcome.

Hi ChrisM,
Timeout issue has been resolved. Below steps are the changes highlighted by your previous posts. The Actual Execution plan is attached also.
1) alter s_picklot.do_no from char(15) to...

Hi ChrisM,

My colleague helped me to try out your restructured script however, the speed is taking longer time to complete than my original one. My original script took...

Hi ChrisM and Grant Fritchey, 
Very much appreciated of your fast reply...