asked on

MYSQL Select to return most recent date and next date in same result row

I have a "Roster" table which has two fields - the userid and the date of their shift. I would like a query that returns a single row for each user, with their most recent past shift and their next future shift. I can get one or the other (ie either past or future) but not both. I'm sure there's some simple answer.

eg if today's date is 2007-03-30
Userid Date
0001 2007-03-02
0002 2007-03-04
0003 2007-03-05
0002 2007-03-06
0001 2007-03-07
0003 2007-04-10
0002 2007-04-11
0002 2007-04-12
0003 2007-04-13
0004 2007-04-14

would return
userid last-shift next-shift
0001 2007-03-07 Null
0002 2007-03-06 2007-04-11
0003 2007-03-05 2007-04-10
0004 Null 2007-04-14

Many thanks in advance
PS I looked at Q22445165 but there don't seem to be the right number of parentheses (brackets) so I couldn't work out how to apply that solution to my problem.

momi_sabag

select t1.userid, max(t1.date) as last_shift, min(t2.date) as next_shift
from mytable t1, mytable t2
where t1.userid = t2.userid
and t1.date < now
and t2.date > now

maybe i'm wrong with the now function - you need the function that returns todays date

momi

momi_sabag

that should be

select t1.userid, max(t1.date) as last_shift, min(t2.date) as next_shift
from mytable t1, mytable t2
where t1.userid = t2.userid
and t1.date < now()
and t2.date > now()

ASKER CERTIFIED SOLUTION

momi_sabag

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mukhtar2t

I create table roster,inserted one userid with 2 dates before and after now and executed this query
SELECT l.userid 'User ID', Max( l.date ) AS 'Last Shift', Min( n.date ) AS 'Next Shift'
FROM roster l
INNER JOIN roster n ON l.userid = n.userid
AND n.date > now( )
WHERE l.date <= now( )
GROUP BY l.userid

and this is the result
Showing rows 0 - 0 (1 total, Query took 0.0530 sec)
User ID Last Shift Next Shift
1 2007-03-29 2007-04-01

David Linker

ASKER

I'm giving the points to momi_sabaq as he provided an answer first - and his query runs faster (0.0115 vs 0.0374 on my real data) than mukhtar2t's solution. However, I had to make a change (moved the "AS" statements into each sub query) to get the query to work so I only gave a B rating:
SELECT t1.userid, last_shift, next_shift
FROM (

SELECT userid, max( date ) AS last_shift
FROM roster
WHERE date <= now( )
GROUP BY userid)t1, (

SELECT userid, min( date ) AS next_shift
FROM roster
WHERE date > now( )
GROUP BY userid)t2,
WHERE t1.userid = t2.userid

David Linker

ASKER

Thanks to both of you for the quick respons and the help.