SQL Server 08: Union over while
A few points:

1 - This is indeed a bad idea.

2 - Your syntax should be updated.  Using implicit JOINs is generally bad since it can lead to a cartesian product if you aren't careful.

3 - Have you looked into using a recursive CTE?

4 - It also looks to me like the row-by-row could be unneeded anyways.  Your date isn't used anywhere in your actual query except to be passed as a field and checked against the subexpression.

5 - Does this return a single row per date?

6 - Finally, #temp tables are dropped when the connection is closed.  You don't need to drop them manually.  If you can create them, as long as you don't make another with the same name in the same connection you won't need to drop it manually.