Omitting in-between dates

Databases
1

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

This query

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

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. :slight_smile:

2

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

3

Thanks Rudy,

Yeah, I’d thought about those issues too and originally planned on doing it in the app. :slight_smile: 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

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

5

Oops. I meant ORDER BY. I was grouping something else right before I wrote that. :stuck_out_tongue:

OK, thanks for the advice!