Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2009
    Posts
    1

    Smile Unanswered: Chalanging query

    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

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Would this challenge be your homework by any chance?

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •