SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Wizard tgavin's Avatar
    Join Date
    Feb 2003
    Location
    FL
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Omitting in-between dates

    I'm retrieving time off requests from mySQL, which gives me ALL of the dates a person has requested

    This query
    Code:
    SELECT 
         c.event_date
        ,c.event_type
        ,m.member_id
        ,m.firstname
        ,m.lastname 
    FROM 
        calendar c
    JOIN 
        members m ON c.lo_id = m.member_id
    WHERE 
        c.event_type <> '' 
        AND 
        c.approved = 0
    gives me this result

    Code:
    2013-05-24    Vacation    9    John Doe
    2013-05-01    Medical     8    Jane Doe
    2013-05-02    Medical     8    Jane Doe
    2013-05-03    Medical     8    Jane Doe
    2013-05-06    Medical     8    Jane Doe
    Now, instead of listing ALL of the days each person has off, I'll only need to get the start date and the end date for each person, so that I can print out the following on the web page

    Requested Time Off
    John Doe: 2013-05-24 (Vaction)
    Jane Doe: 2013-05-01 - 2013-05-06 (Medical)


    As you can (hopefully) tell by this post, I have no idea how to get the start date and end date and omit the in-between dates for each person.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    you probably want start date and end date per person per event type

    the first guess would be to use MIN and MAX with GROUP BY

    but what do you do if jane doe has two separate date ranges for medical (i.e. 1-3, and 6)?

    my advice: read the raw results in sorted sequence into your application language, and do the logic there
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard tgavin's Avatar
    Join Date
    Feb 2003
    Location
    FL
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Rudy,

    Yeah, I'd thought about those issues too and originally planned on doing it in the app. How do you recommend doing the sorting? If I do GROUP BY c.lo_id, c.event_type I only get one result per person.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    no, don't use GROUP BY -- that's for collapsing multiple rows into one aggregate row per group

    use ORDER BY and specify person, then date... and then examine the dates and event types per person in your application logic
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard tgavin's Avatar
    Join Date
    Feb 2003
    Location
    FL
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oops. I meant ORDER BY. I was grouping something else right before I wrote that. :P

    OK, thanks for the advice!


Bookmarks

Posting Permissions

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