Thanks for your suggestions guys. I think it doesn't make any sense for me to spend that much time on this issue since it will take too much time. I...

Thanks for your replies guy. That's what I thought too. These are just 50 records of millions of records as the addresses are for different policy holders. The problem is...

Thanks Chris,

Yeah there were minor alias issues but after correcting them I was able to run the code and get the same result set.

Really appreciate your help !

Well I have attached the requirements document for the view which I'm trying to understand as well. This is the first time I'm working in insurance domain so it's taking...

Any help for the 2nd part guys ?

Grant, can you please give me the link of that article that you've written ?

haha .. yeah it sure does work. They have asked me if I can get the second part of the code done without correlated subqueries as well. This following part...

Luis, your solution is also working fine. Just had few typos in which I've fixed and its giving the right results.

Thanks

Yeah, I will definitely try all three solutions and see which one is better.

Jason, I tried fixing the errors with your code and now I'm getting this error:

Msg 206, Level 16, State 2, Line 1

Operand type clash: date is incompatible with int

which is...

I actually changed it to t and sts aliases to PS later in the code and it works fine.

I'm getting the same result set as original query.

Thanks

Thanks a lot guys for your help. I actually joined this insurance company like a week ago and they have asked me to help improve this code.

I understand that you...

Thanks for your reply Jack.

Basically the reason for creating this view is to get the latest version of policy since there are a lot of of policies in the database....