Generate new table based on date ranges in existing table?

[#1]

Hi All

I have a table where each row with a start date an end date and a few other metrics.  What I want to acheive in the load is to then split out these days into individual rows in a new table with a single date column and linking the same metrics.  I'll try to illustrate below:

Exiting table:

Start_Date     End_Date          Name     Address          etc

01/04/2010     05/04/2010        Fred       1 Anystreet 

01/02/2010     03/02/2010        Bob        2 AnotherStreet 

Desired Result:

Date              Name     Address          etc

01/04/2010     Fred       1 Anystreet

02/04/2010     Fred       1 Anystreet

03/04/2010     Fred       1 Anystreet

04/04/2010     Fred       1 Anystreet

05/04/2010     Fred       1 Anystreet

01/02/2010     Bob        2 AnotherStreet

02/02/2010     Bob        2 AnotherStreet

03/02/2010     Bob        2 AnotherStreet

Please can anyone advise how I could achieve this result?

Thanks

Dominic

[#2]

Really useful Jason, appreciate it, I swapped the code virtually directly and it worked great.  I had to make one small amendment though for anyone else using Jason's example, you'll need to change the ordering of the resident loads to find mindate and maxdate (before the peek's), DESC should be ASC for mindate and ASC should be DESC for maxdate. 

[#3]

Hello,

I've a table with Project Nos., StartdateProject,EnddateProject & Revenue

I need to a create a Calender so that on selecting a particular Year , all the projects starting or ending in this Year are displayed.

Moreover, on selecting the Quarters (Q1 or Q2 or Q3 or Q4) the Revenue should be shown only for that Quarter.

For eg. if a project has Yearly Revenue of 1000 & the project runs from 1 Jan, 2015 to 31 Dec, 2015

then on Selecting Q1, Revenue displayed should be 1000/4(=250).

Please help

Regards