I guess I figured out how to reduce that over 5 hours query to a 5 minutes query (at least for the first run) and that's a really nice improvement.

There might be a better way to do it and maybe I'm doing extrawork during the query but it turned out that the most expensive part in the query is when I filter the information (reducing 71million rows to 1 million) but that could only improve if I had any indexes that change the index scans to index seeks.

I found out that just by organizing the information and process for you (using the articles from Gail Shaw and Jeff Moden) was a great eyesopener on what I could work on.

What I'm doing now is inserting the rows I need into a temporary table and work with that table instead of the view.