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.
LVL 4
David LinkerSystems ArchitectAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

momi_sabagCommented:
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
0
momi_sabagCommented:
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()
0
momi_sabagCommented:
i'm sorry
the query i entered may not be correct,
try this one :

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

that would work
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

mukhtar2tCommented:
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
0
David LinkerSystems ArchitectAuthor Commented:
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
0
David LinkerSystems ArchitectAuthor Commented:
Thanks to both of you for the quick respons and the help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.