tgavin — 2013-04-27T13:27:44-04:00 — #1
I'm retrieving time off requests from mySQL, which gives me ALL of the dates a person has requested
members m ON c.lo_id = m.member_id
c.event_type <> ''
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.
r937 — 2013-04-27T13:38:57-04:00 — #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
tgavin — 2013-04-27T13:57:58-04:00 — #3
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.
r937 — 2013-04-27T14:07:11-04:00 — #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
tgavin — 2013-04-27T14:10:20-04:00 — #5
Oops. I meant ORDER BY. I was grouping something else right before I wrote that.
OK, thanks for the advice!
system — 2014-10-08T00:08:34-04:00 — #6
This topic is now closed. New replies are no longer allowed.