Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: Date Range logic

    Good morning all,

    I've got a little headscratcher for you involving date ranges.

    We have a table for recording absences:
    Absence(unique_identifier, parent_identifier, date_from, date_to ... )
    And an employee table
    Employees(unique_identifier, Surname, Firstname, birth_date ...)

    Where the relationship between the two is:
    Employees.unique_identifier = Absence.parent_identifier

    The problem lies when wanting to know whether an employee was off within a specified date range.

    Absence:
    Code:
    u_id	p_id	date_from	date_to
    1	1	2007-02-01	2007-02-06
    2	2	2007-01-29	2007-02-06
    3	2	2007-03-25	2007-03-25
    4	3	2007-06-06	2007-06-08
    5	4	2007-02-05	2007-02-06
    Given the above sample results, how can I identify which employees were off during the first week of February (2007-02-01 to 2007-02-07)?

    Expected Results:
    Code:
    u_id	p_id	date_from	date_to
    1	1	2007-02-01	2007-02-06
    2	2	2007-01-29	2007-02-06
    5	4	2007-02-05	2007-02-06
    Any advice you can give to help me get the answer I need is much appreciated
    George
    Home | Blog

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Why's the middle line in red? I don't know the SQL for that....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Errr... There was a reason for that, which I now realise is meaningless because I didn't post the SQL I tried already...
    EDIT: I say "I", I actually mean "an employee using the WYSIWYG query editor built into a system.
    Code:
    SELECT p_id FROM Absence WHERE date_from BETWEEN '20070201' AND '20070207'
    Clearly won't return that line, which needs including.

    Did I mention that I want the results in bold, aligned right and every other line needs to be red? I am using Query Analyzer.
    George
    Home | Blog

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I don't get the difficulty. Am I missing something?

    Code:
    SELECT p_id FROM Absence WHERE date_from BETWEEN '20070201' AND '20070207' OR date_to BETWEEN '20070201' AND '20070207'
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Apr 2007
    Posts
    183
    Unless he wants employees that are on leave for the FULL period of
    Feb 1 to Feb 6 ???

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Poots, would the following row be returned?
    Code:
    u_id	p_id	date_from	date_to
    6	7	2007-01-25	2007-03-01
    The above was off during the period in question
    George
    Home | Blog

  7. #7
    Join Date
    Apr 2007
    Posts
    183
    Code:
    SELECT	p_id 
    FROM	Absence 
    WHERE	date_from <= '20070207'
    	AND date_to >= '20070201'

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    Poots, would the following row be returned?
    No - and it illustrates the error in concentrating on inadequate sample data instead of the logic of the problem
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Thanks for the solution Peso and thanks for your help Poots!
    I can't believe how long I've been staring at this and not been able to get my head round a logical answer!
    George
    Home | Blog

Posting Permissions

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