I would imagine returning 4 million + rows would be slow with or without the lookups.

Well how big are the lookup tables and how many of them are there?

One option is to store the cleaned values on the base table and have the...

Something like this?

WITH TEMP_CTE AS(
SELECT *, LAG(DateTimeStamp, 1) OVER(PARTITION BY UID ORDER BY DateTimeStamp ASC) AS PREVIOUS_DATE FROM #test
)
SELECT UID, DateTimeStamp, PREVIOUS_DATE, DATEDIFF(hour,...

I want the import/export wizard to actually work properly all the time.  It's awesome how choosing write your own query messes up consistently.

Hmm.... well in some cases they're not the right tool for the job, I wouldn't call that a disadvantage so much as a use the tool for the job though.

Yes the union will work, just the union without a union all will do a distinct on the entire result set.