Hi Firends i have a challange for all SQL geeks

suppose an employe is allocated for the number of projects and each project is having a "START DATE " and "END DATE " and free time is in which employee has not been allocated to any project.only there is a field of START DATE and END DATE no free time field in database you have to fetch the free time .
take a following example

project1 START DATE = 01/01/2007 and END DATE IS 01/06/2007( free time not stored in database we have to calculate is 02/07/2007 to 31/12/2007)

project2 START DATE = 01/01/2008 and END DATE IS 01/06/2008(02/07/2008 to 31/12/2008)

project3 START DATE = 01/01/2009 and END DATE IS 01/06/2009( free time not stored in database we have to calculate not stored in database we have to calculate is 02/07/2009 to 31/12/2009

write a query in which user give the "start time = 01/01/2007 " and "end time = 31/12/2009 as "

the output of query is all free time slots of employee

thanks

Would this challenge be your homework by any chance?

this problem is a ~lot~ tougher than a homework assignment

see Joe Celko's SQL For Smarties, 3rd ed., pp. 645-8, Section 29.3.1 Gaps in a Time Series

If he builds a table of dates then it becomes a straightforward problem. The only difficult bit is displaying the free dates as separate ranges rather than as individual free dates. Even this would also be simple if it could be done in an external program ie PHP.