Converting date range to list of dates

[Jeff Carr]

Thanks for taking the time to look at this. The problem I have is one that I think should be easy but has me stumped.

I have a list of start and end dates that our equipment is being used. I need to generate a list of all the dates the equipment is in use though for building a calendar that I can export into a downloadable Excel pivot table. (Excel needs data for every day or it won't display dates without data as columns so I'm feeding it null values for every day in my date range and outer joining that data to my real data.)

My table:

ID (int) | Product (varchar) | StartDate (date) | EndDate (date)
+------------------------------------------------------------
| 1 APF 3/19/2007 3/23/2007
| 2 DBEF-II 3/18/2007 4/2/2007
| 3 APF 3/24/2007 3/25/2007
| 4 APF 3/27/2007 3/30/2007
| 5 DBEF-RC 4/2/2007 4/6/2007
| 6 IR Filter 4/4/2007 4/7/2007
| 7 D10 WOW 4/9/2007 4/13/2007
| 8 Top Hat 4/9/2007 4/18/2007
| 9 APF 4/16/2007 4/20/2007
| 10 UV Films 4/20/2007 4/27/2007
| 11 Resin Conductivity 4/23/2007 4/26/2007
| 12 AEPS-MZIP Extrusion 4/29/2007 5/1/2007

It seems simple to change this to a list of days in use, but I can't figure out how to begin. Searching for "query convert date range to date list" in google... well it comes up with everything SQL related on the internet.

For example #1 APF is in use from 3/19/2007 to 3/23/2007 and #2 DBEF-II is in use 3/18/2007 to 4/2/2007, and I want to create a query outputting something like:

Product (varchar) | InUseDate (date)
+------------------------------------------------------------
| APF 3/19/2007
| APF 3/20/2007
| APF 3/21/2007
| APF 3/22/2007
| APF 3/23/2007
| DBEF-II 3/18/2007
| DBEF-II 3/19/2007
| DBEF-II 3/20/2007
....

Any suggestions would be extremely helpful...

[iLLin]

I believe your going to have to use your server side language for this. I use PHP, if thats what you use I have a function for u to get days between start-end.

Let me know.

[Jeff Carr]

Thanks for the quick reply iLLin, but for the moment they haven't decided on a platform so I was trying to do as much as I could in sql. It seems like this should be easily done, but I can't figure out where to start.

They're pushing for Access since they have it on their computers, but I really want to keep the database for this company on an intranet server.