Not a problem... First, it can't be done in a view because of the temp table.

I'm not really using any form of recurrsion... I only stuff that meets the first day's measurments. Then I load the second day's measurements but only if the previous day is present, etc, etc. Only patients with the 5 day loaded have all 5 an in the correct order. It should be much faster than having cascaded joins on 4 million rows 5 times.

I suspect that the only way you'll get any decent performance out of these queries is to build a full blown covering index on you main table.