Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Regular Coder
    Join Date
    Jun 2002
    Location
    England =)
    Posts
    523
    Thanks
    26
    Thanked 0 Times in 0 Posts

    query to get all records between 2 different field dates

    sorry if the question was confusing lol...

    anyway, this is what i need to do but it doesnt work:


    SELECT id, user_worked, approved_by FROM timesheets WHERE timesheet_type='Holiday' AND date_worked >= '2009-03-5' AND date_end <= '2009-03-5' ORDER BY user_worked


    syntax is dodgy, obviously.

    its for a calendar im working on. the problem appears to be that im using 2 date fields (end_date and date_worked [which is the start date really])

    any ideas? i know i could use BETWEEN if there was only 1 date field. but having 2 date fields is confusing me (but needed for the way this application works)

    thanks in advance all!
    "They hired me for my motivational skills. Everyone at work says they have to work much harder when I`m around" Homer J Simpson

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    You should be able to add WHERE date_end >= date_worked AND date_worked <= date_end to close the loose ends of the date range.

  • #3
    Regular Coder
    Join Date
    Jun 2002
    Location
    England =)
    Posts
    523
    Thanks
    26
    Thanked 0 Times in 0 Posts
    ? those are already always right, i check them before inserting into mysql.

    maybe i didnt explain myself very well (v tired today zzzzz)

    basically all holidays have a start date and an end date (end date must be same or after start date [for people booking hols for 1/2 or single days])

    i am then going through a calendar checking if there are any hols for each date. (this is basically a copy/paste from an older system that just created separate records for each date - this was a doddle to get working for that one lol)

    so, for example the date today is 2009-03-03 so i need to get records if this date falls between start_date and end_date.

    does that make any more sense now? need caffeine!
    "They hired me for my motivational skills. Everyone at work says they have to work much harder when I`m around" Homer J Simpson

  • #4
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    No I'm the one thinking with my foot today...

    I guess I would agree with you, your query should work! So... can you show an example of a result you got that you didn't expect to get? Maybe that will help me think.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,857
    Thanks
    78
    Thanked 4,417 Times in 4,382 Posts
    No, I don't see how that works.

    SELECT id, user_worked, approved_by
    FROM timesheets
    WHERE timesheet_type='Holiday'
    AND date_worked >= '2009-03-5' AND date_end <= '2009-03-5'
    ORDER BY user_worked

    Let's say the user took a holiday starting 2009-03-01 and ending 2009-03-31

    If I read you correctly, that means you will have a record that looks like:
    Code:
    timesheet_type :: date_worked :: date_end 
        Holiday    ::  2009-03-01  :: 2009-03-31
    And of course your WHERE clause finds no match on that.

    Right?

    I think you simply have your comparisons backward. Try
    Code:
    AND date_worked <= CurDate() AND CurDate <= date_end
    And note that this means you *CAN* do
    Code:
    AND CurDate BETWEEN date_worked AND date_end
    No???

    [Doesn't have to be CurDate, of course...just use whatever date you want to test for.]

  • Users who have thanked Old Pedant for this post:

    babelfish (03-04-2009)

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,857
    Thanks
    78
    Thanked 4,417 Times in 4,382 Posts
    i am then going through a calendar checking if there are any hols for each date.
    Please don't tell me you are checking each date individually, in a separate SQL query???

    Code:
    SELECT DT.theDate, TS.id, TS.user_worked, TS.approved_by
    FROM tableOfDate AS DT LEFT JOIN timesheets AS TS
    ON ( TS.timesheet_type='Holiday'
            AND DT.theDate BETWEEN TS.date_worked AND TS.date_end )
    ORDER BY DT.theDate, TS.user_worked
    Or something along those lines. Now you'll get all dates, all holidays taken. (The ORDER BY can of course be in the other order, but you will have some records where TS.user_worked is NULL if nobody took a holiday on that date, of course.)

    Hmmm???

  • #7
    Regular Coder
    Join Date
    Jun 2002
    Location
    England =)
    Posts
    523
    Thanks
    26
    Thanked 0 Times in 0 Posts
    [QUOTE=Old Pedant;788782]No, I don't see how that works.
    Code:
    AND CurDate BETWEEN date_worked AND date_end
    QUOTE]

    that worked a treat mate

    Code:
    $query  = "SELECT id, user_worked, approved_by FROM timesheets WHERE timesheet_type='Holiday' AND date_worked <= '$year-$month-$day_num' AND '$year-$month-$day_num' <= date_end ORDER BY user_worked";
    the reason im looking up for each date seperately is that im making a dynamic calendar - so each date is created as a table cell and then we query for any holidays on that day. im not bothered about performance as this is a page that wont be used much (mainly by management) and its only a small project management system.

    thanks for the help though! im still pretty new to sql and php - this is my first app!
    "They hired me for my motivational skills. Everyone at work says they have to work much harder when I`m around" Homer J Simpson


  •  

    Posting Permissions

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