Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2007
    Posts
    77

    Unanswered: Query Problem (beginner)

    Hello,

    I would like to ask for your help on solving a specific query.

    Below here is a sample of records for a certain table that stores the start and end dates of a vacation leave of an employee.

    Let's say I need to produce a report that will display the time record of an employee for a certain date range. I will specify a certain 'start' and 'end' date in order for that to achieve. But before I do that, I need to check records in this "leave" table so that I can display in the report that on a specific day, that employee is "on vacation leave".

    this is the records in the 'leave' table:
    Code:
    Start Date	End Date
    2010-10-4	2010-10-9
    2010-9-26	2010-10-2
    2010-10-30	2010-11-4
    2010-10-25	2010-10-25
    If I specify:
    Code:
    Start date: 2010-10-1
    End date:   2010-10-31
    and would use this as query for retrieving records in the 'leave' table using this statement:
    Code:
    SELECT start_date, end_date FROM leave 
    WHERE start_date >= '2010-10-1' AND end_date <='2010-10-31';
    But then it would only return records that starts from 2010-10-1 and ends in 2010-10-31,
    Code:
    Start Date	End Date
    2010-10-4	2010-10-9
    2010-10-25	2010-10-25
    but I wanted also to get the records that are in the range of that start and end dates like for example the record
    Code:
    Start Date	End Date
    2010-9-26	2010-10-2
    is valid for me because the date 2010-10-1 and 2010-10-2 is needed to provide information for my report which covers the date range of 2010-10-1 up to 2010-10-31.

    How can I do that? Can anyone give me hints or clues? As I myself would also tinker on this problem and would post my answer if I happen to get it right.

    Cheers!
    Programming is fun!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What you want is a leave that occurs within the date range of your query, so you need to use some logic that is counter-intuitive.
    Code:
    SELECT start_date, end_date
       FROM leave
       WHERE start_date <= '2010-10-31'
          AND '2010-10-01' <= end_date;
    This may seem peculiar at first glance. What you really want is leaves that start before your period of interest ends, and that end after your period of interest starts.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Mar 2007
    Posts
    77
    This may have been a very late reply, but thank you for the solution you provided. It was indeed the right one!

    Off topic: Any recommendations or resources (books/sites) in which I can hone my skills in SQL, especially in querying? I know of books by Joe Celko's but maybe some of you can give ideas of where to look for other resources.

    Cheers!
    Programming is fun!

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by Panoy View Post
    TOff topic: Any recommendations or resources (books/sites) in which I can hone my skills in SQL, especially in querying? I know of books by Joe Celko's but maybe some of you can give ideas of where to look for other resources
    I find the examples at A Gentle Introduction to SQL quite nice.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I've read more SQL books than I care to count. There are many SQL books that are good, bad, and in between.

    Joe Celko is the acknowledged master in the field. He can do things that most SQL users will barely understand well enough to put to use, much less actually understand. Joe thinks and writes at a college 300 level most of the time, so he's great reading but not for people getting started!

    For just wrapping your head around queries in general, I currently recommend Simply SQL by our own r937. Rudy and I agree on a lot more than we disagree, although it can be great fun to watch the ensuing chaos during those disagreements!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Mar 2007
    Posts
    77
    Quote Originally Posted by Pat Phelan View Post
    I've read more SQL books than I care to count.
    For just wrapping your head around queries in general, I currently recommend Simply SQL by our own r937. Rudy and I agree on a lot more than we disagree, although it can be great fun to watch the ensuing chaos during those disagreements!
    Yes, you are correct, Simply SQL looks promising.

    Again, off topic (this is getting off by hand):

    How about theoretical books that aid understanding of databases such as discrete math? Correct me if I am wrong on this.

    Does anyone know of popular books?

    Cheers!
    Programming is fun!

  7. #7
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by Panoy View Post
    How about theoretical books that aid understanding of databases such as discrete math? Correct me if I am wrong on this.

    Does anyone know of popular books?
    Not sure what you mean with "descrete math", but C.J. Date's book "SQL and Relational Theory" is all around the concept of relational maths and how it relates to SQL (or doesn't...)

  8. #8
    Join Date
    Mar 2007
    Posts
    77
    Quote Originally Posted by shammat View Post
    Not sure what you mean with "descrete math", but C.J. Date's book "SQL and Relational Theory" is all around the concept of relational maths and how it relates to SQL (or doesn't...)
    Correction, It's "discrete" not "descrete".

    One way or the other (although I'm not in a position to explain it due to my lack of knowledge), discrete math is applied to RDBMS.

    Even a simple web search could give one an idea:
    http://www.ehow.com/info_8368995_app...rete-math.html

    Cheers!
    Programming is fun!

Posting Permissions

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